SQL SERVER IN参数化处理

方法一、

CREATE TABLE [dbo].[Users]
    (
      Id INTEGER IDENTITY(1, 1)
                 PRIMARY KEY ,
      Name NVARCHAR(50) NOT NULL
    ) ;
GO

//循环插值

DECLARE @Counter INTEGER
SET @Counter = 1
WHILE ( @Counter <= 100 )
    BEGIN
        INSERT  Users
                ( Name
                )
        VALUES  ( 'Test Users #' + CAST(@Counter AS VARCHAR(10))
                )
        SET @Counter = @Counter + 1
    END

--拆分函数
CREATE FUNCTION dbo.fnSplit
   (  @List      varchar(8000),
      @Delimiter varchar(5)
   )
   RETURNS @TableOfValues table
      (  RowID   smallint IDENTITY(1,1),
         [Value] varchar(50)
      )
AS
   BEGIN      DECLARE @LenString int       WHILE len( @List ) > 0
         BEGIN             SELECT @LenString =
               (CASE charindex( @Delimiter, @List )
                   WHEN 0 THEN len( @List )
                   ELSE ( charindex( @Delimiter, @List ) -1 )
                END
               )             INSERT INTO @TableOfValues
               SELECT substring( @List, 1, @LenString )            SELECT @List =
               (CASE ( len( @List ) - @LenString )
                   WHEN 0 THEN ''
                   ELSE right( @List, len( @List ) - @LenString - 1 )
                END
               )
         END      RETURN    END

//存储过程

CREATE PROCEDURE [dbo].[spUsers]
    @UsersIDs VARCHAR(8000)
AS
    BEGIN
        SELECT  u.Id ,
                u.Name
        FROM    [dbo].[Users] u
                JOIN dbo.fnSplit(@UsersIDs, ',') t ON u.Id = t.value
    END
GO

//执行

EXECUTE [dbo].[spUsers] '1,2,3,4'

方法二、

CREATE TYPE UsersIDTableType AS TABLE (ID INTEGER PRIMARY KEY);
GO

//存储过程

CREATE PROCEDURE [dbo].[spGetUsersTable]
    @UsersIDs UsersIDTableType READONLY
AS
    BEGIN
        SELECT  c.ID ,
                c.Name
        FROM    [dbo].[Users] c
                JOIN @UsersIDs t ON c.Id = t.ID
    END
GO

//调用

DECLARE @Ids UsersIDTableType
INSERT  @Ids
VALUES  ( 5 )
INSERT  @Ids
VALUES  ( 6 )
INSERT  @Ids
VALUES  ( 7 )
EXECUTE [dbo].[spGetUsersTable] @Ids

//在.NET下如何调用？

调用也比较简单，将参数类型限制为

SqlDbType.Structured

那么值可是是任意IEnumerable, DataTable, 或者DbDataReader。