給SQL Server傳送數組參數的變通辦法

　　最近一直在做Dnn模塊的開發過程中碰到這麼一個問題需要同時插入N條數據不想在程序裡控制但是SQL Sever又不支持數組參數所以只能用變通的辦法了利用SQL Server強大的字符串處理傳把數組格式化為類似
　　
　　然後在存儲過程中用SubString配合CharIndex把分割開來
　　
　　詳細的存儲過程
　　
　　CREATE PROCEDURE dboProductListUpdateSpecialList
　　@ProductId_Array varChar()
　　@ModuleId int
　　AS
　　DECLARE @PointerPrev int
　　DECLARE @PointerCurr int
　　DECLARE @TId int
　　Set @PointerPrev=
　　set @PointerCurr=
　　
　　begin transaction
　　Set NoCount ON
　　delete from ProductListSpecial where ModuleId=@ModuleId
　　
　　Set @PointerCurr=CharIndex(@ProductId_Array@PointerPrev+)
　　set @TId=cast(SUBSTRING(@ProductId_Array@PointerPrev@PointerCurr@PointerPrev) as int)
　　Insert into ProductListSpecial (ModuleIdProductId) Values(@ModuleId@TId)
　　SET @PointerPrev = @PointerCurr
　　while (@PointerPrev+ < LEN(@ProductId_Array))
　　Begin
　　Set @PointerCurr=CharIndex(@ProductId_Array@PointerPrev+)
　　if(@PointerCurr>)
　　Begin
　　set @TId=cast(SUBSTRING(@ProductId_Array@PointerPrev+@PointerCurr@PointerPrev) as int)
　　Insert into ProductListSpecial (ModuleIdProductId) Values(@ModuleId@TId)
　　SET @PointerPrev = @PointerCurr
　　End
　　else
　　Break
　　End
　　
　　set @TId=cast(SUBSTRING(@ProductId_Array@PointerPrev+LEN(@ProductId_Array)@PointerPrev) as int)
　　Insert into ProductListSpecial (ModuleIdProductId) Values(@ModuleId@TId)
　　Set NoCount OFF
　　if @@error=
　　begin
　　commit transaction
　　end
　　else
　　begin
　　rollback transaction
　　end
　　GO
　　
　　網友Bizlogic對此的改進方法:
　　
　　應該用SQL OpenXML更簡單效率更高代碼更可讀
　　
　　CREATE Procedure [dbo][ProductListUpdateSpecialList]
　　(
　　@ProductId_Array NVARCHAR()
　　@ModuleId INT
　　)
　　
　　AS
　　
　　delete from ProductListSpecial where ModuleId=@ModuleId
　　
　　 If empty return
　　IF (@ProductId_Array IS NULL OR LEN(LTRIM(RTRIM(@ProductId_Array))) = )
　　RETURN
　　
　　DECLARE @idoc int
　　
　　EXEC sp_xml_preparedocument @idoc OUTPUT @ProductId_Array
　　
　　Insert into ProductListSpecial (ModuleIdProductId)
　　Select
　　@ModuleIdC[ProductId]
　　FROM
　　OPENXML(@idoc /Products/Product )
　　with (ProductId int ) as C
　　where
　　C[ProductId] is not null
　　
　　EXEC sp_xml_removedocument @idoc
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22113.html