Returning a recordset from a parameterised stored procedure.

[alan_grn]

Has any one been able to return data to QLikview after execting a storeed procedure with varialbles?

I can get the stored procedure to execute, but no results are returned.

[chriscammers]

Hello,

I've built up an example to illustrate

Here is the code to create a stored procedure in SQL Server

Create Procedure dbo.uspTestSQLParameters

@StringParam Varchar(100),

@NumParam Int,

@DateParam Datetime

As

Begin

Select

    @StringParam as YourString,

    @NumParam as YouNum,

    @DateParam as YourDate

End

The trick with calling a procedure is that you have to code the parameters correctly so here is how I call the procedure from Qlikview

//Set variable values for parameters

Set vStringParam = This is your String;

Set vNumParam = 7564;

Set vDateParam = 1/1/1999;

//SQL Execution of a stored procedure

Load *;

SQL EXEC

dbo.uspTestSQLParameters '$(vStringParam)', $(vNumParam), '$(vDateParam)';

You'll notice that I use dollar sign expansion and I use single quotes to encode the string and date. The date value I am using is based on a string and it is important that whatever gets passed to SQL conforms to the required date formatting, I've personall  struggled with dates and knowing weather or not I going to be dealing with the date string or the numeric so some trial and error may be needed to perfect the technique.

thanks

Chris

[chriscammers]

Alan,

First of all, you may not want to include your full signature on these posts, I'd edit that last one and pull off your email and phone numbers .

I'm not sure what to say about your error since you provided so little information about it. Could you post your code or at least a mock up of it? What version of Qlikview are you using? What database is it?

For now all I can tell you is that the sample code I put up works with QV10SR2 and SQL Server 2008R2 The first block of code is to be run on the SQL server to create the procedure.

the second piece calls the procedure from Qlikview.

Thanks

Chris