rajemessage 14195 (3/3/2014)
I have to call few strored precedure like sp1 sp2 sp3 from spmain.
spmain will be scheduled and it will run on time basis.
i want to trap each kind of error for loging so that i can check latter.
we need to roll back only the @mid creating porblme from outer loop of spmain( that is the first loop) , so tha all canges made by the other sub stored proces for that id can be rolled back.
CREATE PROC SPMAIN
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SET NOCOUNT ON
begin LOOP (@mid is not null) -- loops through all id of a table one by one.
if (@i= 1)
EXEC SP1 @mid
if (@i= 2)
EXEC SP1 @mid
EXEC SP2 @mid
IF @@trancount > 0 ROLLBACK TRANSACTION;
SET XACT_ABORT OFF;
To be honest, unless the loops are necessary to support something like sending an email, I'd consider rewriting the whole ball of wax to be set-based instead of RBAR in nature. Stop thinking about rows and start thinking about columns.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Helpful Links:
How to post code problemsHow to post performance problemsForum FAQs