熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

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

2013-11-15 14:38:44  來源: 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
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.