子查询
问题描述:
转换逗号分隔值我有以下查询:子查询
Set @OrderStatuses = 'Cancelled,Complete'
Select *
From TableName
Where Status in (@OrderStatuses)
的Status
就是我从外部接收,也未能取得任何结果作为,其实我们需要处理的是:
Select *
From TableName
Where Status in ('Cancelled', 'Complete');
@OrderStatuses
可以包含任意数量的逗号分隔值。
答
一种方法是动态SQL:
declare @sql nvarchar(max);
set @sql = 'Select * from TableName Where Status in (@list)';
set @sql = replace(@sql, '@list', '@OrderStatuses');
exec sp_executesql @sql;
注意:不能在传递一个列表作为参数。
您还可以使用like
:
Select *
from TableName
Where ',' + @OrderStatuses + ',' like '%,' + Status + ',%';
但是,这不能使用索引的对比。
答
您将需要使用split string function和处理,其余..
;with cte
as
(
select * from split_strings(@orderstatus,',')
)
select * from table where status in (select item from cte)
答
您必须添加的列表作为Table-Valued Parameter
这里有两种方法我用转换项目的任何的IEnumerable列表(这种情况下,整数)为表值参数。您必须为数据库中的结果表创建/定义用户定义类型(UDT)。在UDT下面的例子中被命名为dbo.keyIds
并创建(一次)与该SQL:
CREATE TYPE [dbo].[KeyIds]
AS TABLE(pkId int NOT NULL, PRIMARY KEY CLUSTERED
([pkId] ASC) WITH (IGNORE_DUP_KEY = OFF)
)
C#代码是:
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;
}
}