Cleanup database Transaction--sql statement

Clears all relevant data within the process

EG1:

DECLARE @procedureTemp table
(
[Procedurecode] varchar (10)
)
DECLARE @ProcedureCode varchar (10)

INSERT into @procedureTemp SELECT Procedurecode from Workflowprocedure

While EXISTS (select [Procedurecode] from @procedureTemp)
Begin
--set ROWCOUNT 1--equivalent top (1)
Select Top (1) @ProcedureCode =[procedurecode] from @procedureTemp

DECLARE @caseTemp table
(
[Casecode] varchar (10)
)
DECLARE @caseCode varchar (10)

INSERT into @caseTemp SELECT casecode from workflowcase WHERE procedurecode = @ProcedureCode

While EXISTS (select [Casecode] from @caseTemp)
Begin
--SET ROWCOUNT 1--equals top (1); The key is not to use ' ROWCOUNT ' inside the loop nesting
Select Top (1) @caseCode =[casecode] from @caseTemp

--Delete, Add here
--declare @applicationCode VARCHAR (50)

--select @procedureCode =procedurecode from Workflowprocedure

--print @procedureCode

--set @applicationCode = ' 20140613100462001 '

--select @caseCode =casecode from workflowcase WHERE [email protected] and Applicationcode = @applicationCode

PRINT @caseCode + ' + @ProcedureCode

DELETE from [workflowopinion] WHERE ([casecode][email protected])
DELETE from [Workflowcaseproperty] WHERE ([workflowcasecode][email protected])
DELETE from [Workflowact] WHERE ([casecode][email protected])
DELETE from [Workflowactuser] WHERE ([casecode][email protected])
DELETE from [Workflowcase] WHERE ([casecode][email protected])

--set ROWCOUNT 0
Delete from @caseTemp where [casecode] = @caseCode

End

--set ROWCOUNT 0
Delete from @procedureTemp where [procedurecode] = @ProcedureCode

--print ' Name:----' [email protected]
End