根据传入的两个参数，做分隔更新数据库中的值。

朴灿烈づ我的快乐病毒、 2022-08-04 14:57 112阅读 0赞

alter proc p\_update\_u\_road\_trans\_c\_sendstatus (@comText varchar(8000))  
as  
begin  
create table \#tb(transportNo varchar(20),billno varchar(20),status char(1));  
  
  
\--set @comText='150830001001:1508250094=1,1508250101=1,1508290024=1;150830001002:1508270246=1,1508270249=1,1508270267=1';  
  
set nocount on;  
declare @semicolonlast int,@semicolonCurrent int;  
declare @colonlast int,@colonCurrent int;  
declare @singleCmdText varchar(8000);  
declare @transportNo varchar(20),@SetCmdText varchar(4000);  
declare @commalast int,@commaCurrent int;  
declare @lastCmdText varchar(100);  
declare @billno varchar(20),@status char(1);  
set @semicolonlast=0;  
while 1=1  
begin  
set @semicolonCurrent=CHARINDEX(';',@comText,@semicolonlast);  
if @semicolonCurrent>0  
set @singleCmdText=SUBSTRING(@comText,@semicolonlast,@semicolonCurrent-@semicolonlast);  
else  
set @singleCmdText=SUBSTRING(@comText,@semicolonlast,LEN(@comText)-@semicolonlast+1);  
set @semicolonlast=@semicolonCurrent+1;  
  
set @colonCurrent=CHARINDEX(':',@singleCmdText);  
set @transportNo=SUBSTRING(@singleCmdText,0,@colonCurrent);  
set @SetCmdText=SUBSTRING(@singleCmdText,@colonCurrent+1,LEN(@singleCmdText)-@colonCurrent);  
  
set @commalast=0;  
while 1=1  
begin  
set @commaCurrent=CHARINDEX(',',@SetCmdText,@commalast);  
if @commaCurrent=0  
set @lastCmdText=SUBSTRING(@SetCmdText,@commalast,LEN(@SetCmdText)-@commalast+1);  
else  
set @lastCmdText=SUBSTRING(@SetCmdText,@commalast ,@commaCurrent-@commalast);  
set @commalast=@commaCurrent+1;  
set @billno=SUBSTRING(@lastCmdText,0,LEN(@lastCmdText)-1);  
set @status=RIGHT(@lastCmdText,1);  
insert \#tb(transportNo,billno,status)values(@transportNo,@billno,@status);  
if @commaCurrent=0  
break;  
end  
if @semicolonCurrent=0  
  break;  
end  
  
  
update c set sendstatus=b.status from u\_road\_trans\_c c  inner join \#tb b on  c.billno=b.billno and c.transno=b.transportNo;  
if @@ROWCOUNT=0  
begin  
raiserror('更新失败!',16,1);  
return;  
end  
end