Simsons Simsons - 1 year ago 61
SQL Question

Converting comma-separated value to in subquery

I have following query :

Set @OrderStatuses = 'Cancelled,Complete'

Select *
From TableName
Where Status in (@OrderStatuses)

is what I am receiving externally and it fails to fetch any result as, what we actually need to process is:

Select *
From TableName
Where Status in ('Cancelled', 'Complete');

can contain any number of comma-separated values.

Answer Source

One method is dynamic SQL:

declare @sql nvarchar(max);

set @sql = 'Select * from TableName Where Status in (@list)';

set @sql = replace(@sql, '@list', '@OrderStatuses');

exec sp_executesql @sql;

Note: You cannot pass a list in as a parameter.

You can also use like:

Select *
from TableName
Where ',' + @OrderStatuses + ',' like '%,' + Status + ',%';

However, this cannot use an index for the the comparison.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download