Maintain Transaction even if need to loop through multiple data

I have a ADP app that executes several SPs with a click of a button
Now I want to combine these SP into 1 transaction so that it can
rollback even if one SP fails.
Problem is several SP need to process recordsets in vba so I have made
while loops in vba for them. I do this to avoid cursors as they no good
performers. My choice of avoiding cursors has broken the 'transaction
boundary' therefore I cannot do a roll back for these. How can I still
use store procedures but still maintain performance and be able to roll
back all the SPs if any of them fails?Have you considered rewriting the procs in set based fashion such that
you don't need to call them in a loop? A for each loop is really just a
cursor by another name. Without seeing what you are doing it's
difficult to advise otherwise.
David Portas
SQL Server MVP
--|||Thanks for the hint -> set based technique.
I've found what I've been looking for:
http://groups.google.com/group/micr...8924a2b73888ca4|||I got another problem.
I got this dynamic sp:
create procedure spFindData()
as
declare @.statement varchar(255),@.intYear int,intCount int
set @.intyear = 2010
while @.intyear >=2004
begin
select @.statement = "select count(*) from sales' + @.intYear
-- I want to assign the result of @.statement to @.intCount, how?
if (@.intCount>0)
begin
return @.intYear
end
@.intYear = @.intYear -1
end