SQLServer语句块 记得加事务哦

1.问题描述

先看SQL语句

declare @num nvarchar(100),@CompanyId nvarchar(100),@CompanyName nvarchar(100),@UserId nvarchar(100),@UserName nvarchar(100),@Address nvarchar(100),@Recipient nvarchar(100),@Mobile nvarchar(100),@n int
set @n=1
while(@n<=(select count(1) from 表4 where id not in('20131105130728911120','20131009114531458911','20130926135224993012','20131029113748526011','20131025143000122117','20131024173300791910','20130730101547274696')))
 begin
  select @CompanyId=ID,@CompanyName=Company,@Address=Address,@UserId=userid,@UserName=UserName,@Recipient=Name,@Mobile=Phone from(
  select row_number() over(order by AddTime) as num,* from (
  select 表4.ID,Company,表4.Address,表3.ID as userid,UserName,Name,表3.Phone,表4.AddTime
  from 表4 left join 表3 on 表3.Com_Number=表4.ID where master=1 and 表4.id not in('20131105130728911120','20131009114531458911','20130926135224993012','20131029113748526011','20131025143000122117','20131024173300791910','20130730101547274696')) as a) as b where num=@n
  set @num=replace(replace(replace(replace(CONVERT(nvarchar(100), GETDATE(), 25),'-',''),':',''),'.',''),' ','')+convert(nvarchar(100),cast(floor(rand()*1000) as int))
  print '参数:'+@num+' '+@CompanyId+' '+@CompanyName+' '+@UserId+' '+@UserName+' '+@CompanyName+' '+@Address+' '+@Recipient+' '+@Mobile
  insert into 表1 values(@num,@CompanyId,@CompanyName,@UserId,@UserName,@CompanyName,'23|297|396','',@Address,'200000',@Recipient,@Mobile,getdate())
  update 表2 set InvoiceTitleId=@num where ID in(select ProID from 表5 where ID=@CompanyId)
  set @n=@n+1
  set @num=''
 end

其中 灰色的两个@num插入和修改值不一样 估计可能是执行不同步 语句太快了

2.解决办法:

添加显示事务语句,强制执行完当前语句后执行下一语句

declare @num nvarchar(100),@CompanyId nvarchar(100),@CompanyName nvarchar(100),@UserId nvarchar(100),@UserName nvarchar(100),@Address nvarchar(100),@Recipient nvarchar(100),@Mobile nvarchar(100),@n int
set @n=1
while(@n<=(select count(1) from 表4 where id not in('20131105130728911120','20131009114531458911','20130926135224993012','20131029113748526011','20131025143000122117','20131024173300791910','20130730101547274696')))
	begin
		begin transaction
		select @CompanyId=ID,@CompanyName=Company,@Address=Address,@UserId=userid,@UserName=UserName,@Recipient=Name,@Mobile=Phone from(
		select row_number() over(order by AddTime) as num,* from (
		select 表4.ID,Company,表4.Address,表3.ID as userid,UserName,Name,表3.Phone,表4.AddTime
		from 表4 left join 表3 on 表3.Com_Number=表4.ID where master=1 and 表4.id not in('20131105130728911120','20131009114531458911','20130926135224993012','20131029113748526011','20131025143000122117','20131024173300791910','20130730101547274696')) as a) as b where num=@n
		set @num=replace(replace(replace(replace(CONVERT(nvarchar(100), GETDATE(), 25),'-',''),':',''),'.',''),' ','')+convert(nvarchar(100),cast(floor(rand()*1000) as int))
		print '参数:'+@num+' '+@CompanyId+' '+@CompanyName+' '+@UserId+' '+@UserName+' '+@CompanyName+' '+@Address+' '+@Recipient+' '+@Mobile
		insert into 表1 values(@num,@CompanyId,@CompanyName,@UserId,@UserName,@CompanyName,'23|297|396','',@Address,'200000',@Recipient,@Mobile,getdate())
		update 表2 set InvoiceTitleId=@num where ID in(select ProID from 表5 where ID=@CompanyId)
		set @n=@n+1
		set @num=''
		commit
	end

问题解决~~