ALTER proc [dbo].[Ding_Add]
@UserId int,
@DingDate datetime,
@DaoDate datetime,
@SellDate datetime,
@Name nvarchar(50),
@Tel varchar(50),
@Address nvarchar(50),
@Whatsapp varchar(50),
@ThingId varchar(5000),
@Num varchar(5000),
@Size varchar(5000)
as
begin tran
	Declare @error int
    Declare @thingindex Int,@numindex int,@sizeindex int
    declare @split char(1)
    Set @error=0
    Set @split=','
	Set @thingindex = CharIndex(@split,@ThingId)
	Set @numindex = CharIndex(@split,@Num)
	Set @sizeindex = CharIndex(@split,@Size)
	
	While @thingindex >= 1
	Begin
		IF @thingindex >= 2
		begin
			insert Ding values(@UserId,@DingDate,@DaoDate,@SellDate,@Name,@Tel,@Address,@Whatsapp,Left(@ThingId,@thingindex-1),Left(@Num,@numindex-1),Left(@Size,@sizeindex-1))
			set @error=@error+@@ERROR --记录有可能产生的错误号   
			Set @ThingId = SubString(@ThingId,@thingindex+1,Len(@ThingId)-@thingindex)
			Set @Num = SubString(@Num,@numindex+1,Len(@Num)-@numindex)
			Set @Size = SubString(@Size,@sizeindex+1,Len(@Size)-@sizeindex)
			Set @thingindex = CharIndex(@split,@ThingId)
			Set @numindex = CharIndex(@split,@Num)
			Set @sizeindex = CharIndex(@split,@Size)
		end
	End
	If @ThingId > N''
		insert Ding values(@UserId,@DingDate,@DaoDate,@SellDate,@Name,@Tel,@Address,@Whatsapp,@ThingId,@Num,@Size)
		set @error=@error+@@ERROR --记录有可能产生的错误号   
	--判断事务的提交或者回滚
	if(@error<>0)
	  begin
		rollback transaction
		return 0 --设置操作结果错误标识
	  end
	else
	  begin
		commit transaction
		return 1 --操作成功的标识
	  end
go

• 本文标题： SQL存储过程使用split循环插入数据
• 文章分类：【SQLServer】
• 非特殊说明，本文版权归【胡同里的砖头】个人博客 所有，转载请注明出处.

• 上一篇：project deployment 找不到项目
• 下一篇： SQLServer存储过程游标循环读取数据值