Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

Passing input from qlikview application for a stored proc

Hello,

I am new to Qliview and I am trying to pass variables to a storedproc but it is not returning the expected result set.

Here is my proc:

Create procedure CETCompleted @siteid varchar(10),@startdate datetime,@enddate datetime as

begin

set nocount on;

select phonedata as C from queue where siteid=@siteid and submitted between @startdate and @enddate

select emaildata as E from queue where siteid=@siteid and submitted between @startdate and @enddate

select textdata as T from queue where siteid=@siteid and submitted between @startdate and @enddate

end

Here is my qlikview script:

ODBC Connect to ServerName;

LOAD

Input('Enter SiteID Value', 'Input box') as SiteID,

Input('Enter StartDate Value', 'Input box') as value1_date,

Input('Enter EndDate Value', 'Input box') as value2_date

autogenerate 1;

Let vSiteID = peek('SiteID',0,'Input');

Let vFromDate = peek('value1_date',0,'Input');

Let vDateTo = peek('value2_date',0,'Input');

SQL EXECUTE [dbo].[CETCompleted] @siteid='$(vSiteID)',@startdate='$(vFromDate)',@EndDate='$(vDateTo)'

when I load the script It prompts for siteid, then startdate then end data and after succesfully entering them I get the below error:

I am only seeing field C, siteid, value_date,value2_date but not E and T which are in my storedproc.

All I am trying to do is display values C,E,T in the report. Is this the report

However if i break the actual stored proc into 3 one for c, one for e, one for t then I am able to see all 3 fields and able to put them in a table box. Why wouldnt this show all fields with one stored proc?

Thanks in advance

1 Reply
MVP & Luminary
MVP & Luminary

Re: Passing input from qlikview application for a stored proc

It could be that your approach missed the name of the table from your inputs, like:

Input:

LOAD

Input('Enter SiteID Value', 'Input box') as SiteID,

Input('Enter StartDate Value', 'Input box') as value1_date,

Input('Enter EndDate Value', 'Input box') as value2_date

autogenerate 1;

Let vSiteID = peek('SiteID',0,'Input');

Let vFromDate = peek('value1_date',0,'Input');

Let vDateTo = peek('value2_date',0,'Input');

trace '$(vSiteID)';

and your variables are empty because of this. The next thing would be to check the content of the variables with a trace-statement like above demonstrated.

- Marcus