rajemessage 14195 (3/3/2014)
Dear ALLI 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
AS
BEGIN
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.
BEGIN TRY
BEGIN TRAN
if (@i= 1)
begin
begin loop
EXEC SP1 @mid
end loop
end
if (@i= 2)
begin
EXEC SP1 @mid
begin loop
EXEC SP2 @mid
end loop
end
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION;
EXEC LOGERROR
END CATCH;
end loop
SET XACT_ABORT OFF;
END
GO
yours sincerely
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.
--Jeff Moden
Change is inevitable... Change for the better is not.