为SQL Server传数组参数的变通办法

有色人生

　　最近一直在做Dnn模块的开发,过程中碰到这么一个问题,需要同时插入N条数据,不想在程序里控制,但是SQL Sever又不支持数组参数.所以只能用变通的办法了.利用SQL Server强大的字符串处理传把数组格式化为类似"1,2,3,4,5,6"。 　　 　　然后在存储过程中用SubString配合CharIndex把分割开来 　　 　　详细的存储过程 　　 　　CREATE PROCEDURE dbo.ProductListUpdateSpecialList 　　@ProductId_Array varChar(800), 　　@ModuleId int 　　AS 　　DECLARE @PointerPrev int 　　DECLARE @PointerCurr int 　　DECLARE @TId int 　　Set @PointerPrev=1 　　set @PointerCurr=1 　　 　　begin transaction 　　Set NoCount ON 　　delete from ProductListSpecial where ModuleId=@ModuleId 　　 　　Set @PointerCurr=CharIndex(',',@ProductId_Array,@PointerPrev 1) 　　set @TId=cast(SUBSTRING(@ProductId_Array,@PointerPrev,@PointerCurr-@PointerPrev) as int) 　　Insert into ProductListSpecial (ModuleId,ProductId) Values(@ModuleId,@TId) 　　SET @PointerPrev = @PointerCurr 　　while (@PointerPrev 1 < LEN(@ProductId_Array)) 　　Begin 　　Set @PointerCurr=CharIndex(',',@ProductId_Array,@PointerPrev 1) 　　if(@PointerCurr>0) 　　Begin 　　set @TId=cast(SUBSTRING(@ProductId_Array,@PointerPrev 1,@PointerCurr-@PointerPrev-1) as int) 　　Insert into ProductListSpecial (ModuleId,ProductId) Values(@ModuleId,@TId) 　　SET @PointerPrev = @PointerCurr 　　End 　　else 　　Break 　　End 　　 　　set @TId=cast(SUBSTRING(@ProductId_Array,@PointerPrev 1,LEN(@ProductId_Array)-@PointerPrev) as int) 　　Insert into ProductListSpecial (ModuleId,ProductId) Values(@ModuleId,@TId) 　　Set NoCount OFF 　　if @@error=0 　　begin 　　commit transaction 　　end 　　else 　　begin 　　rollback transaction 　　end 　　GO 　　 　　网友Bizlogic对此的改进方法: 　　 　　应该用SQL2000 OpenXML更简单，效率更高，代码更可读： 　　 　　CREATE Procedure [dbo].[ProductListUpdateSpecialList] 　　( 　　@ProductId_Array NVARCHAR(2000), 　　@ModuleId INT 　　) 　　 　　AS 　　 　　delete from ProductListSpecial where ModuleId=@ModuleId 　　 　　-- If empty, return 　　IF (@ProductId_Array IS NULL OR LEN(LTRIM(RTRIM(@ProductId_Array))) = 0) 　　RETURN 　　 　　DECLARE @idoc int 　　 　　EXEC sp_xml_preparedocument @idoc OUTPUT, @ProductId_Array 　　 　　Insert into ProductListSpecial (ModuleId,ProductId) 　　Select 　　@ModuleId,C.[ProductId] 　　FROM 　　OPENXML(@idoc, '/Products/Product', 3) 　　with (ProductId int ) as C 　　where 　　C.[ProductId] is not null 　　 　　EXEC sp_xml_removedocument @idoc <