有没有办法将值数组作为sql Server 2005的单个参数传递给SP的IN部分？
例如：从MyTable中选择* ID IN(@MyValueArray)
解决方法
在2005年及更早版本中,您不能将数组作为参数传递给存储过程,但要模拟此功能,请将逗号分隔的ID列表作为VARCHAR参数传递.然后,您需要解析此列表,将每个id添加到变量表中.然后在表的结果上使用IN.这不是一个优雅的解决方案,但它是你能做的最好的.
DECLARE @List TABLE (ID INT)
INSERT @List VALUES ('123')
INSERT @List VALUES ('12')
SELECT *
FROM
MyTable
WHERE
MyTableID IN (SELECT ID FROM @List)
这最好通过创建一个函数来实现,该函数将id列表作为字符串接收并将ID列表作为表返回.
IF EXISTS(
SELECT *
FROM sysobjects
WHERE name = 'ParseIDArray')
BEGIN
DROP FUNCTION ParseIDArray
END
GO
CREATE FUNCTION [dbo].[ParseIDArray] (@IDList VARCHAR(8000))
RETURNS
@IDListTable TABLE (ID INT)
AS
BEGIN
DECLARE
--@IDList VARCHAR(100),@LastCommaPosition INT,@NextCommaPosition INT,@EndOfStringPosition INT,@StartOfStringPosition INT,@LengthOfString INT,@IDString VARCHAR(100),@IDValue INT
--SET @IDList = '11,12,113'
SET @LastCommaPosition = 0
SET @NextCommaPosition = -1
IF LTRIM(RTRIM(@IDList)) <> ''
BEGIN
WHILE(@NextCommaPosition <> 0)
BEGIN
SET @NextCommaPosition = CHARINDEX(',',@IDList,@LastCommaPosition + 1)
IF @NextCommaPosition = 0
SET @EndOfStringPosition = LEN(@IDList)
ELSE
SET @EndOfStringPosition = @NextCommaPosition - 1
SET @StartOfStringPosition = @LastCommaPosition + 1
SET @LengthOfString = (@EndOfStringPosition + 1) - @StartOfStringPosition
SET @IDString = SUBSTRING(@IDList,@StartOfStringPosition,@LengthOfString)
IF @IDString <> ''
INSERT @IDListTable VALUES(@IDString)
SET @LastCommaPosition = @NextCommaPosition
END --WHILE(@NextCommaPosition <> 0)
END --IF LTRIM(RTRIM(@IDList)) <> ''
RETURN
ErrorBlock:
RETURN
END --FUNCTION
下面是创建使用此函数接收ID列表的存储过程的示例
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'TestArrayParameter')
BEGIN
DROP PROCEDURE TestArrayParameter
END
GO
CREATE PROCEDURE TestArrayParameter
@ArrayParameter VARCHAR(8000)
AS
BEGIN
SELECT *
FROM TestTable123
WHERE TestTblID IN (SELECT ID FROM [dbo].[ParseIDArray](@ArrayParameter))
-- OR BETTER
SELECT *
FROM
TestTable123 test
INNER JOIN [dbo].[ParseIDArray](@ArrayParameter) list
ON list.ID = test.TestTblID
END
GO
CREATE TABLE TestTable123 (TestTblID INT,TestTblVal VARCHAR(50))
INSERT TestTable123 VALUES (3,'Three')
INSERT TestTable123 VALUES (25,'Twenty Five')
INSERT TestTable123 VALUES (100,'One Hundred')
DECLARE @IDList VARCHAR(8000)
SET @IDList = '25,100'
EXEC TestArrayParameter @IDList
DROP TABLE TestTable123
