ALTER proc [dbo]. [Modifywkcashaccountno]
As
Begin
Set XACT_ABORT on
BEGIN TRANSACTION
DECLARE @errors INT--Number of errors
--Breakdown
while (select COUNT (*) from modifywkcontent where state=0 and results1= ' 0000 ' and results2= ' 0000 ' and results3= ' 0000 ' and results4= ' 0000 ' and results5= ' 0000 '
and results6= ' 0000 ' and results7= ' 0000 ') >0
BEGIN
IF object_id (' Tempdb.. #t ') is not NULL
drop table #t-Deletes a temporary table if it exists
Set @[email Protected][email protected] @error
IF object_id (' Tempdb.. #t2 ') is not NULL
drop table #t2-Deletes a temporary table if it exists
Set @[email Protected][email protected] @error
Select top 1 * into #t from modifywkcontent where state=0 and results1= ' 0000 ' and results2= ' 0000 ' and results3= ' 0000 ' and results4= ' 0000 ' and results5= ' 0000 '
and results6= ' 0000 ' and results7= ' 0000 '
Set @[email Protected][email protected] @error
declare @WLCInvestorApplyID_Messge nvarchar (max);
Set @WLCInvestorApplyID_Messge = (select Wlcinvestorapplyid_messge from #t)
Set @[email Protected][email protected] @error
SELECT * Into #t2 from dbo. Split (@WLCInvestorApplyID_Messge, ', ')
Set @[email Protected][email protected] @error
Insert INTO ModifyWkContent2 select Oldcashaccountno,newcashaccountno,t2.name,results1,results2,results3,results4, Results5,results6,results7,state
From #t2 T2, #t t1
Set @[email Protected][email protected] @error
Update modifywkcontent set state=1 where ID in (select ID from #t)
Set @[email Protected][email protected] @error
IF (select COUNT (*) from modifywkcontent where state=0 and results1= ' 0000 ' and results2= ' 0000 ' and results3= ' 0000 ' and res ults4= ' 0000 ' and results5= ' 0000 '
and results6= ' 0000 ' and results7= ' 0000 ') <=0
Break
ELSE
CONTINUE
END
if (@errors <>0)
Begin
Select ' Error '
Rollback Tran
--return;
End
Else
Begin
Commit
Select ' OK '
End
End
SQL converts a column of data into multiple rows separated by commas