PASSING parameter to store procedure using qlikview Interface

Mon, 17 Aug 2009 16:45:08 GMT

HI

i'm using a stored procedure in qlikview which takes date as input parameter to generate a resultset.

wanted to know how can we pass parameter using qlikview to external program like stored procedure .

following is the procedure we use @ backend in sql

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[BOMofAllItems]
as
begin
create table #aa(item varchar(5))
create table #ItemP(mitem varchar(5),sitem varchar(5),qty real,price real,basic real,tax real,curr varchar(10),cfactor varchar(5))

declare @item varchar(5)
declare @cdate datetime

set @cdate = getdate()

insert into #aa(item)
select distinct ltrim(t_mitm) from ttibom010111

select top 1 @item = item from #aa order by item
while(@item <> '' )
begin
insert into #ItemP(mitem ,sitem ,qty ,price ,basic ,tax ,curr ,cfactor)exec BOMofItem @item,@cdate
delete from #aa where item = @item
set @item = ''
select top 1 @item = item from #aa order by item
end
drop table #aa
select * from #ItemP
drop table #ItemP
end
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

and in qlikview

SQL

Declare

@cdate datetime

execute dbo.[BOMofAllItems]

@cdate = <parameter required>

store BOM into ..\QVD\Purchase_qvd\BOM.qvd;

in this case @cdate requires a date for which the procedure will be executed ....

when i'm passing value thru a variable declared in qv environment its not accepting that value ................

i'm wondering if there is any other method to pass input parameter to SP.

thanks in advance

Re: PASSING parameter to store procedure using qlikview Interface

Tue, 11 Feb 2014 15:59:55 GMT

Hi,

Its been a long time, but may be useful if someone search this again.

The date parameter need to be passed wrapped within the square bracket []. Set a variable which will give your date as [YYYY/MM/DD] and evaluate that date after SQL Exec and pass with stored procedure.

See below the sample script which I pull the modelled calendar from our SQL data warehouse based on fact table's max and min date:

FS:
SQL

EXEC [INFO].[dbo].[spQV_RTT_FreeSlots];

Temp_Calendar_Range:
LOAD

Min

(num

([FS.AppointmentDate]

))as

MinDate

,
Max

(num

([FS.AppointmentDate]

)) as

MaxDate

RESIDENT

[FS];

LET

vMindateFS

='['&date

(Peek

('MinDate',0,'Temp_Calendar_Range'),'YYYY/MM/DD')&']';
LET

vMaxdateFS

='['&date

(Peek

('MaxDate',0,'Temp_Calendar_Range'),'YYYY/MM/DD')&']';

//*/

Calendar:
NOCONCATENATE

LOAD

*,
[date]

[FS.AppointmentDate]

;
SQL

EXEC [INFO].[dbo].[spQV_Calendar_date_Between] $(vMindateFS)

, $(vMaxdateFS)

;

DROP

TABLE

Temp_Calendar_Range;

topic Re: PASSING parameter to store procedure using qlikview Interface in QlikView