Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
rajtechnocraft
Creator
Creator

To pass parameters from QlikView application to a stored Procedure

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,

19 Replies
hector
Specialist
Specialist

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

Not applicable

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

hector
Specialist
Specialist

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!

Not applicable

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

Not applicable

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
Employee
Employee

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
Specialist
Specialist

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

Not applicable

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

Not applicable

thanks Bjorn

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

C Ya

Bjorn_Wedbratt
Employee
Employee

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

Cheers,

Bjorn