mulit value and stored procedures

Question

I have looked at the other questions concerning Multi Value selection in reporting services and none of them fixed my issue.
My issue is this. I have a report that has a query for the parameter - @Type.
Select distinct(type) from TypeTable order by Type

the report paramenter is set up and the report shows all the returned results (currently 3) in the selection box.

My stored procedure has the following clause: where Type in (@Type). The report is only returning the first item in the selection box instead of all three.

Please assist.

thanks!!!

Answer 1

I don't believe it is possible to do an IN ( @type ) statement.

IN must be comma separated, and I don't believe you can pass in @Type as "1,2,3" and have it work.

Perhaps you could parse the @Type variable into a temporary table and then do a JOIN on the temp table. This might be a little slow though.

Answer 2

I'm not sure of the format or data type that is returned by the multi-select list box, but if it is something like:

X,Y,Z

you have two options (I prefer option 1):

option 1
You can split it apart using Arrays and Lists in SQL Server 2005- Using a Table of Numbers. Using that code, you can split the string into a table, with each value in its own row. You can then INNER JOIN that table to your query and filter based on the multiple @Type values.

option 2
Within your stored procedure you can build your query dynamically into a string. You can then EXECUTE (@YourString) and run that query. Your string would look something like:

SET @QUERY='SELECT... FROM ... WHERE ... IN ('+ISNULL(@Type,'')+')'

Look at this link The Curse and Blessings of Dynamic SQL

Answer 3

I've used the following table-variable function to split a list into a table which can be JOIN'd in IN'd.

ALTER FUNCTION [dbo].[Split] ( @List nvarchar(4000), @SplitOn nvarchar(5) ) RETURNS @RtnValue table (
       Id int identity(1,1),  Value nvarchar(100) ) AS BEGIN  WHILE (Charindex(@SplitOn,@List)>0)   BEGIN    Insert Into @RtnValue (value)    Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))    Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))   END  Insert Into @RtnValue (Value)  Select Value = ltrim(rtrim(@List))

    Return END