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。