T-sql 2012 error handling logic

Transact-SQL
1

In a t-sql 2012 database, I am planning to run the following sql unless you make suggestions on how to change the sql.
This is the first time that I have used the following:

  1. rollback and commit, and
  2. try and catch blocks looking for potential errors that can get generated.
    BEGIN TRANSACTION;
    BEGIN TRY
    insert into O.dbo.Stu
    select personID
    ,enrollmentID
    ,attributeID = 3374
    ,value
    ,date
    from O.dbo.Stu
    where attributeID = 997
    insert into O.dbo.Stu
    select personID
    ,enrollmentID
    ,attributeID = 3373
    ,value
    ,date
    from O.dbo.Stu
    where attributeID = 996
    Insert into O.dbo.Stu
    Select c1.personID
    ,c1.enrollmentID
    ,attributeID=case when c2.value = 'N' then 3371 else 3370 end
    ,c1.value
    ,c1.date
    from O.dbo.Stu C1
    LEFT JOIN O.dbo.Stu c2
    on c2.personID=c1.personID
    and (c2.date = c1.date or cast(c2.date as date) = cast(c1.date as date))
    and C2.attributeID= 997
    where C1.attributeID = 1452
    END TRY
    BEGIN CATCH
    If @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
    END CATCH;
    If @@TRANCOUNT > 0
    COMMIT TRANSACTION;
    GO
    There are basically 3 insert statements.
    Thus would you change the t-sql that I listed above where you have a better method on how to handle the
    situation?
2

I would expect you would need something more complex than this. You might be called by something that itself starts a transaction (and want's your changes TOGETHER WITH OTHERS to be considered as a single transaction).

At its most basic you could store the value of @@TRANCOUNT at the start, and later on see if @@TRANCOUNT is different to that original value (and Commit / Rollback accordingly)

But you may want to only COMMIT / ROLLBACK your own section ... rather than whatever is outside your scope.

Then the issue arises if you rollback what does the caller do? well ... my view would be that this routine cannot know that, so it should return an Error Return Code, and the Caller can then rollback, or whatever.

The other issue is that if you ROLLBACK (even to a SavePoint) you probably wreck the ability of the caller to do a ROLLBACK, so what we have found is that it is best to always finish with a COMMIT, so we use SAVE POINTS to achieve that:

BEGIN TRANSACTION MyLabel_01
SAVE TRANSACTION MyLabel_02

Insert / update / Delete rows #1
IF SomeErrorState
BEGIN
	@intMyError = 1234
	GOTO MyProcess_ABORT
END

Insert / update / Delete rows #2
... etc ...

-- fall through if no errors detected earlier

MyProcess_ABORT:

IF @intMyError = 0
BEGIN
	COMMIT TRANSACTION MyLabel_01
END
ELSE
BEGIN
	ROLLBACK TRANSACTION MyLabel_02
	COMMIT TRANSACTION MyLabel_01
	GOTO MyProcess_EXIT
END


MyProcess_EXIT:

RETURN @intMyError

I expect the same can be done with TRY / CATCH. Beware to include tests for the things that can NOT be caught :slight_smile:

3

where is @intMyError = 0 suppose to obtain its value from and be declared about? How can rollback and commit be in the same myProcess_abort? Would you explain to me?

4

That's just an example of a variable that would indicate that an error had occurred. You can use any suitable logic flow that detects an error, this is just an illustration.

You'll need to read up in the DOCs for that.

1 Like