December 28, 2012 at 12:12 pm
Hi All,
I have a procedure that has 5 T-SQL statements to it. These comprise of
-- PART 1 --
Insert into first table
Delete from first table
-- PART 2 --
Insert into a second table
Update my second table
Delete from my second table
Part 1 above preps a second table that helps control the statements in Part 2. In order for part 2 to run both SQL statements in part 1 have to be successful. However the second statement in Part 1 is depended on the first statement in Part 1 running i.e. the INSERT has to run first for the Delete part to make sense.
I have been looking at the TRY/CARCH statements but just wondering what people would recommend in this kind of scenario. If part 1 fails to run correctly and successfully then part 2 would insert, update and delete records that it shouldn't making a bit of a mess.
Just wondering how people control this kind of scenario i.e. tracking SQL and then making a choice as to whether or not to proceed with the next SQL statement.
Currently my T-SQL is a procedure that runs successfully, I have never had an error yet and I run it manually but want to automate it.
Thanks
Eliza
December 28, 2012 at 12:22 pm
TRY/CATCH sounds like the exact thing you need.
begin transaction
begin try
-- PART 1 --
Insert into first table
Delete from first table
-- PART 2 --
Insert into a second table
Update my second table
Delete from my second table
end try
begin catch
rollback transaction
--log the error
--throw an exception?
end catch
Inside your catch you need to rollback your transaction but you can also do some extra stuff like log the error, send an email/text message, rethrow the exception (or a new one).
_______________________________________________________________
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/
December 28, 2012 at 3:33 pm
Sean Lange (12/28/2012)
TRY/CATCH sounds like the exact thing you need.
begin transaction
begin try
-- PART 1 --
Insert into first table
Delete from first table
-- PART 2 --
Insert into a second table
Update my second table
Delete from my second table
end try
begin catch
rollback transaction
--log the error
--throw an exception?
end catch
Inside your catch you need to rollback your transaction but you can also do some extra stuff like log the error, send an email/text message, rethrow the exception (or a new one).
I suggest a couple of minor changes, like so:
begin transaction
begin try
-- PART 1 --
Insert into first table
Delete from first table
-- PART 2 --
Insert into a second table
Update my second table
Delete from my second table
COMMIT TRANSACTION
end try
begin catch
IF XACT_STATE() <> 0
rollback transaction
--log the error
--throw an exception?
end catch
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 29, 2012 at 7:54 am
I have a question. MS documenatation on XACT_STATE shows them checking for XACT_STATE = 1 and doing a commit in the catch block. In what scenario would XACT_STATE be equal to 1 in the Catch block that makes the transaction committable?
Thank you.
December 29, 2012 at 8:22 am
I kind of answered my question...Run this code...I am on SQL Server 2012
IF EXISTS(SELECT NULL FROM sys.objects WHERE name = 'CatchBlockErrorTest')
DROP TABLE dbo.CatchBlockErrorTest
GO
CREATE TABLE dbo.CatchBlockErrorTest(id INT, val varchar(10))
GO
---1st part of test
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO dbo.CatchBlockErrorTest VALUES (1, 'Jam')
INSERT INTO dbo.CatchBlockErrorTest VALUES (1, 'JamJamJamJamJam')
INSERT INTO dbo.CatchBlockErrorTest VALUES (1, 'Sam')
INSERT INTO dbo.CatchBlockErrorTest VALUES (1, 'SamSamSamSamSam')
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
PRINT 'The transaction is in an uncommittable state.' +
' Rolling back transaction.'
ROLLBACK TRANSACTION;
SELECT 'The transaction is in an uncommittable state.' + ' Rolling back transaction.';
END CATCH;
GO
--verify insert
SELECT * FROM dbo.CatchBlockErrorTest
---2nd part of test
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO dbo.CatchBlockErrorTest VALUES (1, 'Jam')
INSERT INTO dbo.CatchBlockErrorTest VALUES (1, 'Sam')
INSERT INTO dbo.CatchBlockErrorTest VALUES (1, 'JamJamJamJamJam')
INSERT INTO dbo.CatchBlockErrorTest VALUES (1, 'SamSamSamSamSam')
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
BEGIN
PRINT 'The transaction is in an uncommittable state.' +
' Rolling back transaction.'
ROLLBACK TRANSACTION;
SELECT 'The transaction is in an uncommittable state.' + ' Rolling back transaction.'
END;
-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT 'The transaction is committable.' +
' Committing transaction.'
COMMIT TRANSACTION;
SELECT 'The transaction is committable.' + ' Committing transaction.'
END;
END CATCH;
GO
--verify insert
SELECT * FROM dbo.CatchBlockErrorTest
A few observations also...
1. You can see that the first two inserts from 2nd part of the test succeed; while the last two inserts from 2nd part fail. Whereas, none of the inserts succeed in the first part.
2. Having XACT_ABORT ON makes the second part of the test behave like the first part.
3. In the 2nd part of the test, if any statements that would succeed are placed after a statement that fails, then those statements do not succeed. For example if the insert order is like
INSERT INTO dbo.CatchBlockErrorTest VALUES (1, 'Jam')
INSERT INTO dbo.CatchBlockErrorTest VALUES (1, 'JamJamJamJamJam')
INSERT INTO dbo.CatchBlockErrorTest VALUES (1, 'Sam')
INSERT INTO dbo.CatchBlockErrorTest VALUES (1, 'SamSamSamSamSam')
then the value 'Jam' would be inserted, but 'Sam' will not be inserted.
4. And if the very first statement of the transaction fails in the 2nd part of the test, then none of the following inserts succeed, even though they would have succeeded otherwise; but the code still goes into XACT_STATE = 1 part of the catch block. To see this change your insert statment order inthe 2nd part as follows:
INSERT INTO dbo.CatchBlockErrorTest VALUES (1, 'JamJamJamJamJam')
INSERT INTO dbo.CatchBlockErrorTest VALUES (1, 'SamSamSamSamSam')
INSERT INTO dbo.CatchBlockErrorTest VALUES (1, 'Jam')
INSERT INTO dbo.CatchBlockErrorTest VALUES (1, 'Sam')
--- Edit to my original post
Now I am wondering if this is not breaking the Atomicity part of the transaction. If not, where would this kind of behavior be desirable. It sort of works like IGNORE_DUP_KEY (sort of, not exactly).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply