Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

SQL Stored Procedure into QlikView

Hi,

i'm currently trying to get data with stored procedures into Qlikview.
Is this possible?

Thanks in advance

Elzo

64 Replies
stephencredmond
Luminary Alumni
Luminary Alumni

Hi Elzo,

It is possible:

LOAD xx, yy;

SQL EXEC sp_xxyy;

But sometimes it is a better idea to go back a step and load the source data from the SP into QV and manipulate it there - you might get more and better results that way.

Stephen

Not applicable
Author

hi elzo....

its possible we working it now will mail u qvw sample in couple of hrs

Anonymous
Not applicable
Author

Hi,

Yes, SQL Server
By using the EXECUTE command in an SQL statement it's possible to execute stored procedures, if the user is granted access to do so on the server. Here are two examples:

Executing sp with input / output parameters

SQL
declare @var3 int
execute sp_myProcedure 5,3, @var3 output
Select @var3 as result;


Executing sp returning a result set

SQL Execute CustOrderHist 'CustomerNo';

ORACLE:

In Oracle, you can call a stored function with a syntax like:

var result number
exec :result := pkg_ims_status.ext_checkdbstatus()

The function will return a value to be stored in the variable result.

You can call the same function in QlikView using an ordinary SQL SELECT-statement like:

SQL SELECT
pkg_ims_status.ext_checkdbstatus() as status;


Running a stored procedure without returning any recordset:

Use the function CALL to specify the name of the stored procedure.

Example (Launches XYZ with the two parameters 2 & 5):

sql call XYZ ( 2 , 5 );

This works for both ODBC and OLE DB. However, when opening the connection using OLE DB, make sure to add (mode is write) before the finishing semi colon in the connection string.


Note1:
You need to make sure that you activated "Open Databases in Read and Write mode" in QlikView. This is done in the script editor on the tab "Settings".

Note2:
If using OLEDB as a provider, you will also need to add the parameter "mode is write" in the OLEDB-connection:
CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=Northwind;Data Source=mySQL;](mode is write);

Not applicable
Author

Thank you so much. You're great!!!

Not applicable
Author

Thanks a lot. It worked for me.

shumailh
Creator III
Creator III

Thanks Johannes for such a detail examples.

Regards,
Shumail Hussain

Not applicable
Author

Hi,

I´m trying to read out parameters from a Oracle Stored Procedures.

Can you help me?

Thanks.

Federico Martino

Not applicable
Author

Hi Archana..

Can you send us a sample document for stored procedure that returns table ?

? can't do it

Thanks..

Not applicable
Author

hi Canolmez,
try this code snippet for any sp which takes two "dates" as input parameter and 1 outparameter. or u can create a new sample sp too if you are comfortable in writing stored procedures.
hopes this works for you
regs
archana
*************************************************************************************************************************

Input:

load

Input ('Enter an from date value', 'Input box') as value1_date,

Input('Enter an end date value', 'Input box') as value2_date

autogenerate 1;

Let FromDate = peek('value1_date',0,'Input');

Let DateTo = peek('value2_date',0,'Input');

sql

DECLARE @date1 datetime,

@date2 datetime,

@vol int

EXEC [dbo].[test_proc] '$(FromDate)', '$(DateTo)', @vol OUTPUT

SELECT @vol as vol