Chinaunix首页 | 论坛 | 博客
  • 博客访问: 694158
  • 博文数量: 463
  • 博客积分: 40000
  • 博客等级: 大将
  • 技术积分: 4580
  • 用 户 组: 普通用户
  • 注册时间: 2008-10-15 16:47
文章分类

全部博文(463)

文章存档

2011年(1)

2008年(462)

我的朋友

分类:

2008-10-15 16:52:10

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

--------------------next---------------------
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

--------------------next---------------------

阅读(352) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~