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。