SQL SERVER IN参数化处理

方法一、

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

//循环插值

DECLARE @Counter INTEGERSET @Counter = 1WHILE ( @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    ENDGO

//执行

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

方法二、

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

//存储过程

CREATE PROCEDURE [dbo].[spGetUsersTable]    @UsersIDs UsersIDTableType READONLYAS     BEGIN        SELECT  c.ID ,                c.Name        FROM    [dbo].[Users] c                JOIN @UsersIDs t ON c.Id = t.ID    ENDGO

//调用

DECLARE @Ids UsersIDTableTypeINSERT  @IdsVALUES  ( 5 )INSERT  @IdsVALUES  ( 6 )INSERT  @IdsVALUES  ( 7 )EXECUTE [dbo].[spGetUsersTable] @Ids

//在.NET下如何调用？

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

SqlDbType.Structured

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