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
问题解决~~