topic Re: Stored Procedure in QlikView

Stored Procedure

Mon, 13 Jul 2015 12:04:36 GMT

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 — Mon, 13 Jul 2015 12:34:23 GMT

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 — Mon, 13 Jul 2015 16:11:09 GMT

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

maxgro — Mon, 13 Jul 2015 16:28:34 GMT

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

trace var=$(var);

load *;

sql execute sp_WhiteDots '$(var)';

Re: Stored Procedure

sinanozdemir — Mon, 13 Jul 2015 16:29:11 GMT

Please see the below script:

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:

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

And here is my stored procedure for this exercise:

hope this helps.