SQLSERVER 存储过程传递数组参数
|
方法一：利用字符串截取拆分数组字符串. 脚本: -->-- ============================================= -- 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(',@P+1) ?? IF(@C> 0) ??? BEGIN ???? SET @Nums=CAST (SUBSTRING(@StrArray,@P+1,@C-@P-1) AS INT) ???? INSERT INTO #t_info(UserName,@Nums) ???? SET @P=@C ??? END ?? ELSE ??? BREAK? ? END SET @Nums=CAST(SUBSTRING(@StrArray,LEN(@StrArray)-@P) AS INT) INSERT INTO #t_info(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 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |