Friday, October 23, 2009

Multi-Value Parameters, Stored Procedures and IN operator

Today, I was adding parameters to my Reporting Services report and made a very common mistake when using multi-value parameters. My assumption:
SELECT * FROM [SomeTable] WHERE ID IN ( @multiValueParameter ) 
No wonder it didn't work since this is interpreted as:
SELECT * FROM [SomeTable] WHERE  ID IN ( '1,2,3,4' )
instead of
SELECT * FROM [SomeTable] WHERE  ID IN ( '1','2','3','4' )
I recalled I had done this before in some other project and after a visit to the archive I found it.
Following is the solution (You will find same logic elsewhere but with use of a Split function to return table whereas I create a table in my procedure in other words skip creating a function):
DECLARE @separator_position INT -- This is used to locate each separator character
DECLARE @array_value VARCHAR(1000)-- this holds each array value as it is returned
DECLARE @TableOfArrayItems TABLE (Item NVARCHAR(100) collate database_default )  DECLARE @separator char(1) --Used in WHERE clause
BEGIN
      SET @separator =',' --Separator A.K.A. Delimiter
      SET @MultiValueFromRS = @MultiValueFromRS + @separator --append ',' at the end
      WHILE PATINDEX('%' + @separator + '%', @OpportunityLevel) <> 0
      BEGIN -- patindex matches the a pattern against a string
             SELECT @separator_position = PATINDEX('%' + @separator + '%',@MultiValueFromRS)
             SELECT @array_value = LEFT(@MultiValueFromRS, @separator_position - 1)  
             INSERT INTO @TableOfArrayItems(Item) VALUES(@array_value)
             
             --Moving to end of array
             SELECT @MultiValueFromRS = STUFF( @MultiValueFromRS, 1, @separator_position, '')
      END 
END    
And now you can use @TableOfArrayItems anywhere in you query/stored procedure like:
SELECT * FROM [SomeTable] WHERE ID IN ( SELECT ID FROM @TableOfArrayItems)         
And success!

External References:
  1. The curse and blessing of dynamic SQL 
  2. Using Multi-Value Parameters in Stored Procedures 
  3. Passing multi-value parameter in stored procedure (SSRS report)

1 comment: