4 Replies Latest reply: Dec 26, 2012 1:45 AM by sai v RSS

    How to call stored procedure???

    Sushil Kumar

      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???
          Sushil Kumar

          Please help me guys

            • Re: How to call stored procedure???
              Shumail Hussain

               

              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???
                Christian Conejero

                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???
                sai v

                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