Passing Multivalued Variables to a Stored Procedure

I have a table called Stores that contains information about the grocery stores in the chain I work for. I need to write a stored procedure that accepts a list of store IDs as an argument and returns details about those stores. If I know the list of store IDs ahead of time—for example 210 and 213—I can use the following query:

SELECT *FROM StoresWHERE storeid IN(210,213)

However, when I don't know the IDs, I can create the @storeids VARCHAR(1000) input parameter for my stored procedure and change the WHERE clause as follows:

WHERE storeid IN(@storeids)

If I pass one store ID, as the following sample EXEC statement shows:

EXEC GetStores @storeids = '210'

the new WHERE clause works. But if I pass the multiple store IDs that the following EXEC statement shows:

EXEC GetStores @storeids = '210, 213'

the query returns no records. I can use dynamic execution to solve the problem by constructing my entire T-SQL statement in a variable such as @strsql, then using EXEC (@strsql) to execute it. However, I'd rather not implement this solution because the user executing the stored procedure must have direct permissions for the statement executed dynamically and not just EXECUTE permissions on the stored procedure. How can I pass variables that contain multiple values to my stored procedure without using dynamic execution?

If you prefer not to construct the query dynamically and execute it, you can use string-manipulation functions in a static query to return the result you want. I learned the trick I'm going to use in the solution from SQL Server MVP Fernando G. Guerrero. The following query, which uses the Authors table in the Pubs sample database, shows how to pass multivalued variables:

USE pubsDECLARE @lnames AS varchar(1000)SET @lnames = 'White,Greene,Carson'SELECT * FROM AuthorsWHERE CHARINDEX(',' + au_lname + ',', ',' + @lnames + ',') > 0

This successful query doesn't require dynamic execution. Here's how the query works. The CHARINDEX() function looks for the au_lname column wrapped with commas inside the @lnames variable wrapped with commas. The reason for wrapping both the au_lname column and the @lnames variable with commas is to avoid an incorrect match when you're looking for a value that's also a substring of another value. For example, suppose you're looking for the name 'White,Greene,Carson'. An author in the Authors table has the last name Greene, and another author has the last name Green. By looking for ',' + au_lname + ',' in ',' + @lnames + ',' you ensure that the query doesn't return Green. Note that if the @lnames variable contains spaces—for example, 'White, Greene, Carson'—you must remove them because the CHARINDEX() function treats a space as any other character, and when looking for ',Greene,' in ',White, Greene, Carson,', the query won't find a match. You can use the REPLACE() function to replace all spaces in the variable with empty strings, as the following query shows:

USE pubsDECLARE @lnames AS varchar(1000)SET @lnames = 'White, Greene, Carson'SELECT * FROM AuthorsWHERE CHARINDEX(',' + au_lname + ',', ',' +   REPLACE(@lnames, ' ', '') + ',') > 0

Note, however, that if performance is important, you're better off using dynamic execution, which lets you use an index on au_lname. The solution that uses static T-SQL requires a table scan.