To pass parameters from QlikView application to a stored Procedure

[rajtechnocraft]

Dear All,

I want to pass parametrs from qlikview application to a stored procedure

Right now i am using hard coded parametrs from SP and directly calling SP into Qlikview application

Can kindly any one help me on this urgently.

I will be thankful to you guys.

Regards

Raj,

[hector]

why you change the '0' by dates?

0 represents the first record, you can't change this, because the table says autogenerate (1)

c ya

[#3]

oh ............... i thought '0' was for integer type ,,,,,,,,,, thatz y replaced it with date as i wanted to pass date as input variable

[hector]

I made this test

SQL Server 2000

CREATE PROCEDURE [dbo].[sp_test] @param1 as int
AS

Select @param1*2 as result
GO

QlikView

ODBC CONNECT TO [My_Server] (XUserId is bMaAdZFMIF);

SQL
exec HM..sp_test 1;

and qlikview gave me the result column

Check the parameters

C ya!

[#5]

i tried changing following code

Create Procedure test_proc @date1 datetime, @date2 Datetime, @Vol Float Output
AS
Begin
Select @Vol = Max(Volume) From Test Where date1 >= @date1 and date2 <= @date2
End

this is my proc @ backend which accepts two input parametes date1 & date2 ...... based on that it returns output in @vol.

code i'm using in QV

Input:

load

Input('Enter an from date value', 'Input box') as value1_date,
Input('Enter an end date value', 'Input box') as value2_date
autogenerate 1;

Let FromDate = peek('value1_date',0,'Input');
Let DateTo = peek('value2_date,0,'Input');

sql

Declare
@vol int,
@FromDate datetime,
@DateTo datetime

set @FromDate = '$(FromDate)'

set @DateTo = '$(DateTo)'

Execute [dbo].[test_proc] @FromDate, @DateTo, @vol = @vol output

Select @vol as @vol

when i refresh my appn it asks me for two input dates , but doesnt display any output. column (in my case @vol) .............. .. dont know why

[#6]

hi Hector ........

when i pass hard coded values my sp is running perfectly fine, problem arises when i use input variables to pass values........... go thru code i sent you again........... you missing a line there

archanah

[Bjorn_Wedbratt]

If the sp is running fine in QV when hardcoding values, like:

sql

Declare
@vol int,

Execute [dbo].[test_proc] '2008/01/04', '2009/01/04', @vol output

Select @vol as vol

the only thing I can think of is that you're getting a miss-match of datatypes when using variables. I wonder if QV is picking up your variables as numeric values? Hmm... could try to add a text-object with an expression like =num('$FromDate') to see if QV will recognize it as a date (it should then convert to numeric format).

I tried both ODBC and OLEDB calling another sp from QV with the following, and it's working just fine:

Input:
Load input('Start Date','Input box') as val_start,
input('End Date','Input box') as val_end
autogenerate 1;
Let vStart=peek('val_start',0,'Input');
Let vEnd=text(peek('val_end',0,'Input'));
SQL /****** Script for SelectTopNRows command from SSMS ******/
declare @inv float
Execute dbo.spInv '$(vStart)', '$(vEnd)', @inv out
Select @inv as tmp
;

I know that datatypes, especially when it comes to date-formats can be a bit tricky so it could be worth a shot to create a simple stored procedure with integers, instead of datetime. Something like:

CREATE PROCEDURE [dbo].[test] (
 -- Add the parameters for the stored procedure here
 @input1 int,
 @input2 int,
 @parmOut int Output )
AS
BEGIN
 set @parmOut = @input1 + @input2
END

and in QV:

input:
Load
input('val1','Input box') as val1,
input('val2','Input box') as val2
autogenerate 1;
Let val1 = peek('val1',0,'input');
let val2 = peek('val2',0,'input');
SQL
declare @out int
Execute dbo.test '$(val1)','$(val2)',@out output
Select @out as tmp;

[hector]

I agree with Bjorn check the formats and the the internal values, sometimes for the date values QlikView shows 01/01/2009 but internally is a number 39000 something

I don't know, maybe you can pass the parameter something like 20090101 and internally in your sp (SQL Sintax) use the convert/cast function.

you can use the trace function or "Generate Log file" issue.

c ya

[#9]

hi Bjorn.................

thanks 4 ur reply ............. it was really helpful .............

sometime qlikview behaves so stupidly ........................ i tried date function in two different appn... it is returning me date and in second it returns number......... i'm so confused.....................

anyidea y this cud be happening

[#10]

thanks Bjorn

my sp is working fine now.............. it was taking date as num............... thanks againg for solving my prob

C Ya

[Bjorn_Wedbratt]

Great! Please don't forget to mark the answer to help others finding it.

Cheers,

Bjorn