Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
oh ............... i thought '0' was for integer type ,,,,,,,,,, thatz y replaced it with date as i wanted to pass date as input variable
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!
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
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
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;
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
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
thanks Bjorn
my sp is working fine now.............. it was taking date as num............... thanks againg for solving my prob
C Ya
Great! Please don't forget to mark the answer to help others finding it.
Cheers,
Bjorn