Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Stored Procedure

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!

4 Replies

Re: Stored Procedure

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

Gabriel
Valued Contributor II

Re: Stored Procedure

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

MVP
MVP

Re: Stored Procedure

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

trace var=$(var);

T:

load *;

sql execute sp_WhiteDots '$(var)';

sinanozdemir
Valued Contributor III

Re: Stored Procedure

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.

Community Browser