Passing the output of a multiselect to a Stored Procedure

[denvercoder9]

 Hello,

I am trying to get the resulting list of a multi select to be sent as one of the parmeters of the stored procedure for the report. The miltiselect is populated based on the client that is selected in an input control above. This is the code for that:  

EXECUTE [shared].[qp].[GetClientNames] $P!{clientId}  (returns a single field that is both shown and the value)

That all works fine. Where i am running into an issue is that it does not allow the list to be passed to the stored procedure. I cannot simply put the contents of the stored procedure in the report query because it has other elements that it was not liking such as declares and the like. Also the issue would come up again because the stored procedure that is being called is calling another (sort of. it is replacing some thigs and running the code. It is complicated to explain here).

So the question is, is there a way to cpnvert the list to a sting to be passed into the stored procedure? If not is there a way to do this at all? 

 

Thanks in advance.

Edit: Just to clerify. I am using Microsoft SQL.

Post Edited by denvercoder9 at 07/11/2012 19:33

Post Edited by denvercoder9 at 07/11/2012 19:34

[cmatthews]

 Do you already end up with a list like,

1,2,3,4,5,6

Or is your list row based in the first place? I have dealt with this a number of ways and it depends on the format of the field that you are passing.

Of course if you just pass '1,2,3,4,5,6' to the procedure with single quotes around it and your param is string type then it will be fine. Then I have some nifty functions that can make that comma seperated list into something more friendly for non-dynamic SQL use.

I put a funciton in the code block that may be usefor for you.

 

Code:

CREATE FUNCTION [dbo].[ufn_ParseString2Table](@String VARCHAR(8000),@Delimiter VARCHAR(10) = ',')RETURNS @ReturnTable TABLE(Item VARCHAR(100))ASBEGIN/*Example:SELECT CONVERT(INT,ITEM) FROM dbo.ufn_ParseString2Table ('39, 549, 324, 3556, 24, 2132, 345 ,', ',') */WHILE CHARINDEX(@Delimiter,@String)>0BEGIN	INSERT @ReturnTable	SELECT SUBSTRING(@String,1,(CHARINDEX(@Delimiter,@String)-1))	SELECT @String=RIGHT(@String,DATALENGTH(@String)-CHARINDEX(@Delimiter,@String))ENDINSERT @ReturnTable SELECT @String RETURNEND

[denvercoder9]

The issue is that I need to be able to turn the list into a string like that because just passing the list will not work, I am assuming because it is a complex data type.

My report runs this:

[shared].[run].[MissingPDE] $P{clientId},$P{clientNames},$P{startDate},$P{endDate}

where $P{clientNames} needs to be changed so that it is converted to a string like I was saying. Or is there some other way to approach this that is more appropriate. I am still rather new to iReport and JasperServer.

[denvercoder9]

 I figured it out. If you put an ! before the varriable name it works. (after you set the defalut value of the parameter to something like [1234])

Code:

[shared].[run].[MissingPDE] $P{clientId},$P!{clientNames},$P{startDate},$P{endDate}