September 2, 2009 at 4:16 pm
Hi Guys,
We are converting some legacy client based code into server based SQL statements.
The concern I have is what happens if we have a several connected statements to make a procedure, and there is an error part way through, or perhaps a power cut etc.
Is there a facility to create a series of linked statements that if there is an error it rolls back anything it has already done. I thought about temp tables, but it seems a lot of work and was hoping that there was an inbuilt function that already exists.
Thanks for any suggestions.
Matt
September 2, 2009 at 4:51 pm
Try this:
--============================================================================
-- For multiple ops in a script, keep a running count of errors.
-- For any error count over 0, rollback all ops
BEGIN TRANSACTION T1
Declare @ErrorCountInt
SET @ErrorCount = 0
--
--OP#1 code goes here
--
IF @@ERROR 0
SET @ErrorCount = @ErrorCount + 1
--
--OP#2 code goes here
--
IF @@ERROR 0
SET @ErrorCount = @ErrorCount + 1
--
--OP#(n) code goes here
--
IF @@ERROR 0
SET @ErrorCount = @ErrorCount + 1
-- Finalize - if any errors occured, rollback all
IF @ErrorCount = 0
BEGIN
COMMIT TRANSACTION T1
END
ELSE
BEGIN
ROLLBACK TRANSACTION T1
END
GO
This could definitely be optimized for a given situation, but it gives the general idea
-MarkO
"You do not really understand something until you can explain it to your grandmother" - Albert Einstein
September 2, 2009 at 5:23 pm
Hi, that's great I'll give it a go. Thank you
September 7, 2009 at 9:43 pm
Assuming your inline comamnds are plain SQL or through exceptions when there are internal erros, you could simply use the built in mechanism in SQL 2005:
BEGIN TRY
BEGIN TRAN
September 7, 2009 at 9:45 pm
Assuming your inline comamnds are plain SQL or through exceptions when there are internal erros, you could simply use the built in mechanism in SQL 2005:
BEGIN TRY
BEGIN TRAN
--ALL OF YOUR SQL CODE GOES HERE
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
declare @m varchar(1000)
SET @m = ERROR_MESSAGE()
RAISERROR (@m,15,1)
END CATCH
September 7, 2009 at 9:48 pm
Assuming your inline comamnds are plain SQL or through exceptions when there are internal erros, you could simply use the built in mechanism in SQL 2005:
BEGIN TRY
BEGIN TRAN
--ALL OF YOUR SQL CODE GOES HERE
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
declare @m varchar(1000)
SET @m = ERROR_MESSAGE()
RAISERROR (@m,15,1)
END CATCH
September 7, 2009 at 11:48 pm
benyos (9/7/2009)
Assuming your inline comamnds are plain SQL or through exceptions when there are internal erros, you could simply use the built in mechanism in SQL 2005:
You should check XACT_STATE() before calling ROLLBACK. (It might return zero.)
TRY...CATCH cannot be used in a user-defined function.
Also ERROR_MESSAGE() returns NVARCHAR(2048), not VARCHAR(1000).
The principle is correct though.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply