topic Re: How to call stored procedure??? in QlikView

How to call stored procedure???

sushil353 — Thu, 14 Jul 2011 05:16:41 GMT

Hi All,

I have a stored procedure written in SQL. The Procedure having a refcurser to return a complete table.. I want to call that procedure in qlikview and load the data. the body of the procedure is given below..

Please help me out as i am new to this situation...

begin

-- Call the procedure

defects_bsn_pkg.defects_bsn_load_proc(p_source_schema => :p_source_schema,

p_start_date => :p_start_date,

p_end_date => :p_end_date,

p_dl_op_date_start => :p_dl_op_date_start,

p_dl_op_date_end => :p_dl_op_date_end,

p_ot_code => :p_ot_code,

p_prd_group => :p_prd_group,

p_location_code => :p_location_code,

p_dt_type => :p_dt_type,

p_di_info => :p_di_info,

po_refcur => :po_refcur);

end;

How to call stored procedure???

sushil353 — Thu, 14 Jul 2011 06:04:34 GMT

Please help me guys

Re: How to call stored procedure???

shumailh — Thu, 14 Jul 2011 08:06:31 GMT

Define SQL Connection and sql statement as below

CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=RF;Data Source=172.1.1.1; Use Procedure for Prepare=1;Auto Translate=True;
Packet Size=4096;Workstation ID=ABC;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is ABCDEFGH);
  
SQL EXEC DatabaseName.dbo.ProcedureName @Para1 = $(vPara1), @Para2 = $(vPara2), @Para3 = $(vPara3) ;

Easy way is to try OLEDB Connection and Select Wizard

Shumail

How to call stored procedure???

christian77 — Thu, 14 Jul 2011 08:09:06 GMT

Hi, I have this from other discussion.

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 (sp con input / output parámetros)

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

Executing sp returning a result set (sp con resultado)
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: (sp sin retorno) (non query)

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.

Si es OLEDB añade (mode is write) al final de la cadena de conexión.

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".

Asegúrate de que está activado Open DataBase en modo de lectura y escritura ()

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);

Si es OLEDB añade (mode is write) al final de la cadena de conexión.

Re: How to call stored procedure???

Wed, 26 Dec 2012 06:45:58 GMT

Hi Sushil,

If you are using Microsoft SQL Server as the database, then please perform below steps:

Database : OLE DB -> Connect -> Select Microsoft OLEDB Provider for SQL Sever / SQL Native Client -> and in the Connection Tab -> Enter your server details, Database details and click Test Connection.

If the result is success then click OK.

Now try to execute the query by: sql exec sp_assetslist;

If there are any parameters to the sproc : sql exec sp_assetslist $(Param1), $(Param2), ... ;

Hope this might help.

Thanks,

Sai Krishna