如果在IN子句中存储了以逗号(，)分隔的字符串，则可以使用charindex函数获取值。如果使用. net，则可以使用SqlParameters进行映射。

DDL Script:

CREATE TABLE Tags
    ([ID] int, [Name] varchar(20))
;

INSERT INTO Tags
    ([ID], [Name])
VALUES
    (1, 'ruby'),
    (2, 'rails'),
    (3, 'scruffy'),
    (4, 'rubyonrails')
;

t - sql:

DECLARE @Param nvarchar(max)

SET @Param = 'ruby,rails,scruffy,rubyonrails'

SELECT * FROM Tags
WHERE CharIndex(Name,@Param)>0

您可以在. net代码中使用上述语句并将参数映射为SqlParameter。

提琴手演示

编辑: 使用下面的脚本创建名为SelectedTags的表。

DDL Script:

Create table SelectedTags
(Name nvarchar(20));

INSERT INTO SelectedTags values ('ruby'),('rails')

t - sql:

DECLARE @list nvarchar(max)
SELECT @list=coalesce(@list+',','')+st.Name FROM SelectedTags st

SELECT * FROM Tags
WHERE CharIndex(Name,@Param)>0

在我看来，解决这个问题的最佳来源是这个网站上发布的内容:

Syscomments。Dinakar Nethi

CREATE FUNCTION dbo.fnParseArray (@Array VARCHAR(1000),@separator CHAR(1))
RETURNS @T Table (col1 varchar(50))
AS 
BEGIN
 --DECLARE @T Table (col1 varchar(50))  
 -- @Array is the array we wish to parse
 -- @Separator is the separator charactor such as a comma
 DECLARE @separator_position INT -- This is used to locate each separator character
 DECLARE @array_value VARCHAR(1000) -- this holds each array value as it is returned
 -- For my loop to work I need an extra separator at the end. I always look to the
 -- left of the separator character for each array value

 SET @array = @array + @separator

 -- Loop through the string searching for separtor characters
 WHILE PATINDEX('%' + @separator + '%', @array) <> 0 
 BEGIN
    -- patindex matches the a pattern against a string
    SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
    SELECT @array_value = LEFT(@array, @separator_position - 1)
    -- This is where you process the values passed.
    INSERT into @T VALUES (@array_value)    
    -- Replace this select statement with your processing
    -- @array_value holds the value of this element of the array
    -- This replaces what we just processed with and empty string
    SELECT @array = STUFF(@array, 1, @separator_position, '')
 END
 RETURN 
END

Use:

SELECT * FROM dbo.fnParseArray('a,b,c,d,e,f', ',')

致谢:Dinakar Nethi

如果在IN子句中存储了以逗号(，)分隔的字符串，则可以使用charindex函数获取值。如果使用. net，则可以使用SqlParameters进行映射。

DDL Script:

CREATE TABLE Tags
    ([ID] int, [Name] varchar(20))
;

INSERT INTO Tags
    ([ID], [Name])
VALUES
    (1, 'ruby'),
    (2, 'rails'),
    (3, 'scruffy'),
    (4, 'rubyonrails')
;

t - sql:

DECLARE @Param nvarchar(max)

SET @Param = 'ruby,rails,scruffy,rubyonrails'

SELECT * FROM Tags
WHERE CharIndex(Name,@Param)>0

您可以在. net代码中使用上述语句并将参数映射为SqlParameter。

提琴手演示

编辑: 使用下面的脚本创建名为SelectedTags的表。

DDL Script:

Create table SelectedTags
(Name nvarchar(20));

INSERT INTO SelectedTags values ('ruby'),('rails')

t - sql:

DECLARE @list nvarchar(max)
SELECT @list=coalesce(@list+',','')+st.Name FROM SelectedTags st

SELECT * FROM Tags
WHERE CharIndex(Name,@Param)>0

步骤1:-

string[] Ids = new string[] { "3", "6", "14" };
string IdsSP = string.Format("'|{0}|'", string.Join("|", Ids));

步骤2:-

@CurrentShipmentStatusIdArray [nvarchar](255) = NULL

步骤3:-

Where @CurrentShipmentStatusIdArray is null or @CurrentShipmentStatusIdArray LIKE '%|' + convert(nvarchar,Shipments.CurrentShipmentStatusId) + '|%'

or

Where @CurrentShipmentStatusIdArray is null or @CurrentShipmentStatusIdArray LIKE '%|' + Shipments.CurrentShipmentStatusId+ '|%'

    create FUNCTION [dbo].[ConvertStringToList]


      (@str VARCHAR (MAX), @delimeter CHAR (1))
        RETURNS 
        @result TABLE (
            [ID] INT NULL)
    AS
    BEG

IN

    DECLARE @x XML 
    SET @x = '<t>' + REPLACE(@str, @delimeter, '</t><t>') + '</t>'

    INSERT INTO @result
    SELECT DISTINCT x.i.value('.', 'int') AS token
    FROM @x.nodes('//t') x(i)
    ORDER BY 1

RETURN
END

——你的查询

select * from table where id in ([dbo].[ConvertStringToList(YOUR comma separated string ,',')])

对于SQL Server 2008，可以使用表值参数。这有点麻烦，但可以说比我的其他方法更干净。

首先，您必须创建一个类型

CREATE TYPE dbo.TagNamesTableType AS TABLE ( Name nvarchar(50) )

然后，你的ADO。NET代码如下所示:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
cmd.CommandText = "SELECT Tags.* FROM Tags JOIN @tagNames as P ON Tags.Name = P.Name";

// value must be IEnumerable<SqlDataRecord>
cmd.Parameters.AddWithValue("@tagNames", tags.AsSqlDataRecord("Name")).SqlDbType = SqlDbType.Structured;
cmd.Parameters["@tagNames"].TypeName = "dbo.TagNamesTableType";

// Extension method for converting IEnumerable<string> to IEnumerable<SqlDataRecord>
public static IEnumerable<SqlDataRecord> AsSqlDataRecord(this IEnumerable<string> values, string columnName) {
    if (values == null || !values.Any()) return null; // Annoying, but SqlClient wants null instead of 0 rows
    var firstRecord = values.First();
    var metadata= new SqlMetaData(columnName, SqlDbType.NVarChar, 50); //50 as per SQL Type
    return values.Select(v => 
    {
       var r = new SqlDataRecord(metadata);
       r.SetValues(v);
       return r;
    });
}

更新 根据@Doug

请尽量避免var metadata = SqlMetaData。InferFromValue (firstRecord columnName);

它设置了第一个值的长度，所以如果第一个值是3个字符，那么它设置的最大长度为3，如果超过3个字符，其他记录将被截断。

因此，请尝试使用:var metadata= new SqlMetaData(columnName, SqlDbType. xml)。NVarChar maxLen);

注意:最大长度为-1。