SQL Server存储过程数组参数
SQL Server存储过程不支持数组参数类型，要传递数量不定的参数，需要采用其他方式进行传递
方法一：利用字符串截取拆分数组字符串
-- =============================================
-- Author:
-- Create date: <2010-11-26>
-- Description: <存储过程数组参数>
-- 方法一:利用字符串截取拆分数组字符串
-- =============================================
CREATE PROCEDURE SP_ParameterArray
@UserName VARCHAR(10),
@StrArray VARCHAR(800)
AS
DECLARE @P INT
DECLARE @C INT
DECLARE @Nums INT
SET @P=1
SET @C=1
BEGIN TRANSACTION
Set NOCOUNT ON
CREATE TABLE #t_info
(
UserName VARCHAR(10),
Nums INT
)
SET @C=CHARINDEX(',',@StrArray,@P+1)
SET @Nums=CAST(SUBSTRING(@StrArray,@P,@C-@P) AS INT)
INSERT INTO #t_info(UserName,Nums) VALUES(@UserName,@Nums)
SET @P=@C
WHILE (@P+1< LEN(@StrArray))
BEGIN
SET @C=CHARINDEX(',',@StrArray,@P+1)
IF(@C> 0)
BEGIN
SET @Nums=CAST (SUBSTRING(@StrArray,@P+1,@C-@P-1) AS INT)
INSERT INTO #t_info(UserName,Nums) VALUES(@UserName,@Nums)
SET @P=@C
END
ELSE
BREAK
END
SET @Nums=CAST(SUBSTRING(@StrArray,@P+1,LEN(@StrArray)-@P) AS INT)
INSERT INTO #t_info(UserName,Nums) VALUES(@UserName,@Nums)
SELECT * FROM #t_info
DROP TABLE #t_info
SET NOCOUNT OFF
IF @@ERROR=0
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END
GO
方法二：利用OpenXML 效率更高
-- =============================================
-- Author:
-- Create date: <2010-11-26>
-- Description: <存储过程数组参数>
-- 方法二:利用OpenXML 效率更高
-- @XML_Array='
-- =============================================
CREATE PROCEDURE [dbo].[SP_ParameterArray2]
@UserName VARCHAR(10),
@XML_Array nvarchar(500)
AS
BEGIN
IF (@XML_Array IS NULL OR LEN(LTRIM(RTRIM(@XML_Array))) = 0)
RETURN
CREATE TABLE #t_info(UserName VARCHAR(10),Nums INT)
DECLARE @IdHandel INT
EXEC sp_xml_preparedocument @IdHandel OUTPUT, @XML_Array
INSERT INTO #t_info(UserName,Nums)
SELECT @UserName,N.Nums
FROM OPENXML(@IdHandel,'/Array/PA') WITH(Nums INT) AS N
WHERE N.[Nums] IS NOT NULL
EXEC sp_xml_removedocument @IdHandel
SELECT * FROM #t_info
DROP TABLE #t_info
END