When I execute the query
SELECT TOP 10 * FROM claim
WHERE status IN ('paid, 'denied')
I get 10 rows. However, when I try to use a variable to specify the IN list, as in the following query, I receive no rows:
DECLARE @status varchar(20)
SET @status='paid'' +','+''denied''
PRINT @status
SELECT TOP 10 * FROM claim
WHERE status IN (@status)
Why doesn't the second query return the same results as the first one?
As you discovered, you can't use a variable to specify a query's IN list. However, you can use dynamic string execution to get around this restriction and take advantage of the flexibility of using variables—which can be passed from your application's user interface, usually through a set of check boxes.
In dynamic string execution, you construct a variable that contains the complete SQL query, then execute the query by using either the sp_executesql system stored procedure or the EXEC() function. So in your case, you can append to the query the IN list that your variable specifies. Listing 1 shows an example of using sp_executesql to execute the string.
The downside to dynamic string execution is that the execution context is always that of the caller, meaning you might need to construct views to give the calling applications access to the appropriate columns in the underlying tables. SQL Server 2005 removes this execution-context restriction, letting enterprise applications more easily and effectively use dynamic string execution.
—Patrick Conlan
Platform Program Manager,
Microsoft Project Team