要求表中的id有在applyrecordid 中的天数都加 adddate ，在同一个事务中。
调试工具还不错
CREATE PROCEDURE dbo.up_applyrecord_updatedate(@adddate int,@applyrecordid char(300))
WITH RECOMPILE
AS
BEGIN
declare @indexInString int
declare @indexNext int
declare @leftstr char(500)
declare @str char(500)
declare @valuestr char(500)
declare @currentLen int
create table #tabletemp (id char(300))
set @leftstr = @applyrecordid
set @indexInString = charindex(',',@leftstr)
if(@indexInString=0)
begin
set @valuestr = @leftstr
insert into #tabletemp(id)values(@valuestr)
end
else
begin
set @indexInString = charindex(',',@leftstr)-1
set @valuestr = substring(@leftstr,1,@indexInString)
set @indexInString = @indexInString +2
set @leftstr = substring(@leftstr,@indexInString,datalength(@leftstr))
set @currentLen = datalength(@leftstr)
set @indexInString = charindex(',',@leftstr)
insert into #tabletemp(id)values(@valuestr)
while(@currentLen>0 and @indexInString>0)
begin
set @indexInString = charindex(',',@leftstr)
if(@indexInString=0)
set @valuestr = @leftstr
else
begin
set @indexInString = charindex(',',@leftstr)-1
set @valuestr = substring(@leftstr,1,@indexInString)
set @indexInString = @indexInString +2
set @leftstr = substring(@leftstr,@indexInString,datalength(@leftstr))
set @currentLen = datalength(@leftstr)
set @indexInString = charindex(',',@leftstr)
end
insert into #tabletemp(id)values(@valuestr)
end
end
BEGIN TRAN
update t_applyRecord set inputDate =dateadd(dd,@adddate,birthday) where id in(select id from #tabletemp)
update APPROVE_OPINION set APPROVEDATETIME=dateadd(dd,@adddate,birthday) where MPID in (select MPID from IWF_PROCESS_BIZ where BIZID in(select id from #tabletemp))
IF (@@error!=0)
BEGIN
ROLLBACK TRAN
RETURN(1)
END
COMMIT TRAN
RETURN(0)
drop table #tabletemp
END
