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)