方法一、

    CREATE TABLE [dbo].[Users]

    (

      Id INTEGER IDENTITY(1, 1)

                 PRIMARY KEY ,

      Name NVARCHAR(50) NOT NULL

    ) ;

GO
  

//循環(huán)插值

    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
  

?

    --拆分函數(shù)

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 


  

//存儲(chǔ)過程

    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
  

//執(zhí)行

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

方法二、

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

GO
  

//存儲(chǔ)過程

    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
  

//調(diào)用

    DECLARE @Ids UsersIDTableType

INSERT  @Ids

VALUES  ( 5 )

INSERT  @Ids

VALUES  ( 6 )

INSERT  @Ids

VALUES  ( 7 )

EXECUTE [dbo].[spGetUsersTable] @Ids
  

//在.NET下如何調(diào)用？

調(diào)用也比較簡(jiǎn)單，將參數(shù)類型限制為

    SqlDbType.Structured
  

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

SQL SERVER IN參數(shù)化處理