福州信息网
美食
当前位置:首页 > 美食

怎样同时插入N条数据而不在程序里控制

发布时间:2019-09-13 20:03:32 编辑:笔名

问:“怎样同时插入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

改进方法:

应该用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

宝宝手心出汗
汉森四磨汤适用人群
宝宝咳嗽吃什么食物好
儿童化痰的药哪个好