Question:
I have a stored procedure that takes a costcenterid (int) as an input parameter. The user wants to search on an unspecified number of costcenters each time. I use the ‘IN’ clause to find the cost center. But how do I pass these unspecified number of costcenter IDs to my stored procedure?

Answer:
You can create an IN clause with a variable but you must use the EXECUTE statement. For example, let’s use a simple query that has an IN clause from the Northwind database:

SELECT * FROM CategoriesWHERE CategoryName IN ('Beverages', 'Condiments')

This returns two categories. Here’s the syntax that uses a variable for the IN clause:

DECLARE @InString varchar(255)SET @InString = "'Beverages', 'Condiments'"EXEC ( 'SELECT * FROM CategoriesWHERE CategoryName IN ('+ @InString +')')

This syntax returns the same results as the previous query. In a stored procedure the variable would be passed as a parameter and used in the same way. Since you’re passing all the cost centers as a single string, the number can vary from query to query.