CODEWITHSUNDEEP
sqlsql-server-2008-r2
Simsons
Simsons

Reputation: 12735

Converting comma-separated value to in subquery

I have following query : 

Set @OrderStatuses = 'Cancelled,Complete'  

Select * 
From TableName  
Where Status in (@OrderStatuses)

The Status 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');

@OrderStatuses can contain any number of comma-separated values.

Upvotes: 0

Views: 93

Answers (3)

Charles Bretana
Charles Bretana

Reputation: 146409

You must add the list as a Table-Valued Parameter

Here are two methods I use to convert any IEnumerable list of items (in this case, integers) into a Table Valued Parameter. You have to have created/defined a User Defined type (UDT) for the resultant table in your database. In the example below the UDT is named dbo.keyIds and is created (once) with this SQL:

CREATE TYPE [dbo].[KeyIds] 
AS TABLE(pkId int NOT NULL, PRIMARY KEY CLUSTERED 
   ( [pkId] ASC) WITH (IGNORE_DUP_KEY = OFF)
 )

The c# code is: 

public class DbParamList : List<IDbDataParameter>
{ 
    public void AddSQLTableParm<T>(
                string parmName, IEnumerable<T> values)
    {
        var parm = new SqlParameter(parmName, CreateDataTable(values))
        {
            SqlDbType = SqlDbType.Structured,
            TypeName = "dbo.keyIds"
        };
        Add(parm);
    }
    internal static DataTable CreateDataTable<T>(IEnumerable<T> values)
    {
        var dt = new DataTable();
        var props = typeof (T).GetProperties();
        if (props.Length > 0)
        {
            foreach (var col in props)
                dt.Columns.Add(col.Name, col.PropertyType);
            foreach (var id in values)
            {
                var newRow = dt.NewRow();
                foreach (var prop in id.GetType().GetProperties())
                    newRow[prop.Name] = prop.GetValue(id, null);
                dt.Rows.Add(newRow);
            }
        }
        else
        {
            dt.Columns.Add("ids");
            foreach (var id in values) dt.Rows.Add(id);
        }
        return dt;
    }
}

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28890

You will need to use split string function and process the rest..

;with cte
as
(
select * from split_strings(@orderstatus,',')
)
select * from table where status in (select item from cte)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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.

Upvotes: 2

Related Questions