4 Replies Latest reply: Jul 13, 2015 12:29 PM by Sinan Ozdemir RSS

    Stored Procedure

    Naama Mor

      Hi,

      I want to load data from stored procedure into qlikview.

      the SP gets an input (date) and returns AgencyId and HotelId.

      I created an ODBC connection and then:

       

      SP:

      sql

      DECLARE @d date = getdate()-1

      EXEC sp_WhiteDots @d;

       

      the sql script works in Microsoft SQL server.

      but there are no results in qlikview (no errors either).

       

      i also tried :

      load AgencyId, HotelId;

      sql

      DECLARE @d date = getdate()-1

      EXEC sp_WhiteDots @d;

       

       

      but then i got this error :

      Field not found - <AgencyId>

      sql

      DECLARE @d date = getdate()-1

      EXEC sp_WhiteDots @d

       

       

      help please!

        • Re: Stored Procedure
          Marcus Sommer

          You could only use these functions and syntax within your sql-statement which the database and the odbc-driver support. I would it try something easier like:

           

          Load *;

          SQL SELECT YourFields From YourProcedure;

           

          - Marcus

          • Re: Stored Procedure
            Gabriel Oluwaseye

            Hi,

             

            I would suggest create a QlikView variable to hold DECLARE @d date = getdate()-1 and pass the variable into your SQL

            EXEC sp_WhiteDots @d.

             

             

            Hope this help

            • Re: Stored Procedure
              Massimo Grossi

              let var= date(today()-1, 'YYYYMMDD');

              trace var=$(var);

               

              T:

              load *;

              sql execute sp_WhiteDots '$(var)';

              • Re: Stored Procedure
                Sinan Ozdemir

                Please see the below script:

                Capture.PNG

                 

                As you can see I created a variable called vParam which is equivalent of GETDATE() - 1. Although, pay attention to the date format 'YYYY-MM-DD', this is SQL Server default date format.

                 

                The original table had the below data:

                 

                Capture3.PNG

                 

                After I ran the QV script, I got the below results which gave every record that didn't have today's date:

                 

                Capture2.PNG

                 

                And here is my stored procedure for this exercise:

                 

                Capture4.PNG

                 

                hope this helps.