SQL Server存储过程不支持数组参数类型，要传递数量不定的参数，需要采用其他方式进行传递
方法一：利用字符串截取拆分数组字符串
-- =============================================
-- Author:<lx>
-- 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:<lx>
-- Create date: <2010-11-26>
-- Description: <存储过程数组参数>
-- 方法二:利用OpenXML 效率更高
-- @XML_Array='<Array><PA Nums="1"></PA><PA Nums="2"></PA><PA Nums="3"></PA><PA Nums="4"></PA></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
本文介绍了两种在SQL Server存储过程中处理数组参数的方法。一种是通过字符串截取和拆分来实现，另一种则是使用OpenXML进行更高效的数据解析。这两种方法为解决存储过程中无法直接接受数组类型参数的问题提供了实用方案。
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
