topic How to pass variable to a SqlServer stored proc in QlikView

How to pass variable to a SqlServer stored proc

Tue, 25 Jun 2013 19:37:43 GMT

Hello All:

I have a sqlserver stored proc

which is executed as

exec SampleTotals @MyDate='2013-01-01', @Region= 'OH'

in Qlikview i am calling this stored proc as

SQL

exec SampleTotals @Mydate = '2013-01-01' ,@Region = OH;

I get the data when i execute like this.

I want pass Today() in place of '2013-01-01'

I tried the following

Let @vDate = Date(Today(),'YYYY-MM-DD');

SQL

Declare @vDate = DateTime

exec SampleTotals @MyDate = @vDate,@Region = 'OH'

I don't get any errors but don't get any data.

I even try to hardcode the value of Let @vDate = '2013-01-01' but still the no data.

Please let me know what i am missing.

Gysbert_Wassenaar — Tue, 25 Jun 2013 19:57:51 GMT

Does your stored procedure expect a string or a number?

LET vDate = '2013-01-01'; //string

Result:

SQL exec SampleTotals @MyDate= '$(vDate)';

LET vDate = num(today()); //number

Result:

SQL exec SampleTotals @MyDate= $(vDate);

Tue, 25 Jun 2013 20:03:12 GMT

I think you need the quotes around variable to ensure it is passed as string to sqlserver:

Let @vDate = Date(Today(),'YYYY-MM-DD');

SQL exec SampleTotals @MyDate = '$(vDate)', @Region = 'OH'

Wed, 26 Jun 2013 12:52:56 GMT

Thanks for responding.

My Stored proc expects datetime and i was missing the quotes but still i get no results.

i typed, i used '2013-01-01' because i know the data exists for this date.

Let @vDate = '2013-01-01';

SQL
Declare @vDate DateTime

//exec SampleTotals @MyDate='2013-01-01', @Region= 'OH';

exec SampleTotals @MyDate= '$(vDate)', @Mill='Demopolis';

it executes fine but no rows.