March 3, 2014 at 6:05 am
Dear ALL
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 when error comes inside the outer loop for @mid ,
so that all canges made by sub stored proces for that @mid can be rolled back,
but we want to continue with next @mid .
--------------
CREATE PROC SPMAIN
AS
BEGIN
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SET NOCOUNT ON
--outer loop
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
March 12, 2014 at 9:13 pm
Man I don't know if that's real code of pseudo code (which I hope it is) because I've never seen BEGIN LOOP 😀
Something like this might work (if you give actual code, I can provide a better example)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.
if (@i= 1)
begin
begin loop
BEGIN TRY
BEGIN TRANSACTION
EXEC SP1 @mid
IF @@ERROR = 0
COMMIT TRANSACTION
END TRY
BEGIN CATCH
EXEC LOGERROR
ROLLBACK TRANSACTION;
END CATCH;
end loop
end
if (@i= 2)
begin
BEGIN TRY
BEGIN TRANSACTION
EXEC SP1 @mid
IF @@ERROR = 0
BEGIN
/* If the EXEC above succeeded, continue into the next loop */
COMMIT TRANSACTION
begin loop
BEGIN TRY
/* Start next TRY, if all is good commit the transaction and move on,
otherwise, roll back and exit the loop */
BEGIN TRANSACTION
EXEC SP2 @mid
IF @@ERROR = 0
COMMIT TRANSACTION
END TRY
BEGIN CATCH
EXEC LOGERROR
ROLLBACK TRANSACTION;
/* There was an error in the INNER loop, don't continue to process
so exit the loop */
BREAK
END CATCH;
end loop
END
END TRY
BEGIN CATCH
EXEC LOGERROR
ROLLBACK TRANSACTION;
END CATCH;
end
end loop
SET XACT_ABORT OFF;
END
GO
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 12, 2014 at 11:44 pm
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.
March 14, 2014 at 6:08 am
Few part is algorithm like loop.
and i want to roll back only if error comes any where in side outer loop,
and after error handeling i want continue with next id of outer loop.
March 14, 2014 at 7:46 am
rajemessage 14195 (3/14/2014)
Few part is algorithm like loop.and i want to roll back only if error comes any where in side outer loop,
and after error handeling i want continue with next id of outer loop.
With that explanation I would agree with Jeff 100%. This could stand to have major overhaul. There is no need for looping to handle this kind of thing. You write set based logic for each step and the stuff that doesn't work you insert into an audit table so you know it "failed".
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 19, 2014 at 8:12 am
We are considering set base apporch for new devlopment
but following is quite complicated SP, which i can not change now.
So please tel me technically the way i have used transaction are ok or not.
March 19, 2014 at 8:22 am
rajemessage 14195 (3/19/2014)
We are considering set base apporch for new devlopmentbut following is quite complicated SP, which i can not change now.
So please tel me technically the way i have used transaction are ok or not.
It should work, did you look at my example?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 19, 2014 at 8:32 am
Unfortunately without seeing the actual code it is hard to tell you if you are doing things correctly or not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply