Multi value param with Sproc

  • dean gross

    SSCrazy

    Points: 2632

    how do I create the WHERE clause in a sproc to handle a parameter from reporting services 2005 that can send multiple values?

    I am trying

    WHERE jm.SectorNum IN (@strSectorNum) but this does not seem to be working

    Thanks

    Dean

  • adam.blackwell

    Grasshopper

    Points: 11

    I have done this for a recent report. I first created a function called dbo.fn_MultiSelectStr that is passed the parameter value. It then adds quotes around each selected value, i.e. looks for the commas and adds single quotes before and after. The end value should then look something like the following:

    'value1','value2','value3'

    Your sp will have to create dynamic sql to execute, i.e.

    @sql = 'select * from table1 where '

    set @STR = dbo.fn_MultiSelectStr(@param1)

    set @sql = @sql + 'field1 in (' + @STR + ')'

    exec (@sql)

    Hope this makes sense

    Adam

  • Marianne L Collins

    SSCommitted

    Points: 1895

    If you want to avoid dynamic sql, you can create a function that parses out the comma-separated values and puts them in a table.

    CREATE FUNCTION [dbo].[fn_ParseComma] (@STRING nvarchar(max))

    RETURNS @Tmp TABLE

    (mystr nvarchar(150))

    AS

    BEGIN

    DECLARE @Count int

    SELECT @Count = 0

    WHILE (SELECT CHARINDEX(',', @STRING)) > 0

    BEGIN

    INSERT @Tmp SELECT LTRIM(RTRIM(SUBSTRING(@STRING, 1, CHARINDEX(',', @STRING)-1)))

    SELECT @STRING = LTRIM(RTRIM(SUBSTRING(@STRING, CHARINDEX(',', @STRING)+1, LEN(@STRING))))

    END

    INSERT @Tmp SELECT LTRIM(RTRIM(@STRING))

    RETURN

    END

    For example – for a field called OperatingUnit (parameter is @OU):

    OperatingUnit IN (SELECT mystr FROM dbo.fn_ParseComma (@OU))

    If the list is going to be quite long, we sometimes create a temp table to hold the parameter values, and then join to the temp table.

    INSERT INTO #OU

    SELECT mystr as OU FROM dbo.fn_ParseComma (@OU)

    A co-worker of mine wrote this, and we use it all the time for our multi-value parameters. Hope this helps!

    -Marianne

  • sentekinn

    SSC Rookie

    Points: 31

    where in(@Code) it works

    but where in('"+parameters!Code.Value+"') I am writing but it doesnt work why??

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply