We found a pretty easy to use and reliable way of doing this using a split function we built that returns the parameter array as a table. This way, we don't have to worry about dynamic SQL or SQL injection. Here's the function code and how we use it. The nice part about this function is that we can pass it whatever is needed for the delimiter. In this case, becuase SSRS returns multi-select parms as a comma seperated list, we use the comma. The following example will only return results from the main table where the category matches those selected in the parameters, whether it's 1 or 100 categories selected.

Example:

DECLARE @Categories VARCHAR(MAX)

SET @Categories = 'VALUE1, VALUE2, VALUE3, ..., VALUE100' (this would be sent to the sproc by SSRS)

SELECT *

FROMMainTable a

inner join Reporting.dbo.ParmSplit(@Categories,',') b on a.Category = b.Items

-- =============================================

-- Description:Function splits a delimited string into

--seperate records and returns a table

--containing the individual values

-- =============================================

CREATE FUNCTION [dbo].[ParmSplit](@String varchar(max), @Delimiter char(1))

RETURNS @Results TABLE (Items varchar(max))

AS

BEGIN

DECLARE @index INT, @CNT INT

DECLARE @VALUE varchar(max)

SELECT @index = 1, @CNT = 1 -- Initialize the variables

WHILE @index !=0

BEGIN

SELECT @index = CHARINDEX(@Delimiter,@STRING) -- Find the first split location

IF @index !=0

SELECT @VALUE = LEFT(@STRING,@INDEX - 1) -- Slice it up

ELSE

BEGIN

SELECT @VALUE = @STRING

SELECT @CNT = 10000

END

INSERT INTO @Results(Items) VALUES(@VALUE) -- Store the results

SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @index) -- Remove used slice

SELECT @CNT = @CNT + 1 -- See if we are done

IF LEN(@STRING) = 0 BREAK

IF @CNT > 8000 BREAK -- Limit to 8000 items

END

RETURN

END

Addressing the last limit question, although not the cleaneast method, you could change the 8000 limit in the function (or make it a parmater of the function), and set the limit to 5, thereby limiting the selection to the first 5 selected in the list. This wouldn't be very clear for the users, but it would work to limit the result set.