方法一、
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。