方法一:利用字符串截取拆分数组字符串.
脚本:
-->-- =============================================
-- 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 效率更高
脚本:
-->-- =============================================
-- 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