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