Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
sushil353
Honored Contributor II

How to call stored procedure???

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 => Smiley Tongue_source_schema,

                                        p_start_date => Smiley Tongue_start_date,

                                        p_end_date => Smiley Tongue_end_date,

                                        p_dl_op_date_start => Smiley Tongue_dl_op_date_start,

                                        p_dl_op_date_end => Smiley Tongue_dl_op_date_end,

                                        p_ot_code => Smiley Tongue_ot_code,

                                        p_prd_group => Smiley Tongue_prd_group,

                                        p_location_code => Smiley Tongue_location_code,

                                        p_dt_type => Smiley Tongue_dt_type,

                                        p_di_info => Smiley Tongue_di_info,

                                        po_refcur => Smiley Tongueo_refcur);

end;

4 Replies
sushil353
Honored Contributor II

How to call stored procedure???

Please help me guys

shumailh
Contributor III

Re: How to call stored procedure???

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

christian77
Valued Contributor

How to call stored procedure???

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.

Not applicable

Re: How to call stored procedure???

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

Community Browser