Template for Stored Procedures

Question

Template for Stored Procedures

podgehb

SSC Enthusiast

Points: 135
More actions
September 10, 2005 at 9:22 am

#89763

I am building a template to use for stored procedures that includes error handling and rolls back transactions. If anyone can see any faults in the template or can suggest improvements, please let me know.
By the way, checking the @rowcount after executing SQL is optional and will change depending on how many rows the developer expects to update (if any).
Thanks,
Craig
create proc StoredProcedureTemplate
@Parm1 char(1),
@Parm2 int
as
set nocount on
declare @err int
declare @rowcount int
declare @retvalue int
-- assertion
if @Parm1 not in ('A', 'B', 'C')
begin
raiserror('Parm1 needs to be A, B, C', 16, 1)
return 50000
end
begin transaction
-- run SQL
update Table1
set col1 = 'x'
select @err=@@error, @rowcount=@@rowcount
if (@err!=0) or (@rowcount<1) goto ErrorHandler

-- exec SP
exec @retvalue = AnotherStoredProcedure
select @err=@@error
if (@err!=0) or (@retvalue!=0) goto ErrorHandler
commit transaction
return 0 -- success
ErrorHandler:
if (@@trancount!=0) rollback transaction
if (@err!=0) return @err -- error
else if (@retvalue!=0) return @retvalue -- error returned from SP
else return -999 -- incorrect rowcount

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

I'm thinking that maybe a header with the purpose of the proc, usage, and a little revision history might be something to consider... and, maybe, make all of the SQL reserved words upper case like EM does...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 2

I'm not sure about @@trancount. It will count all transaction started within the connection - in procedure called this SP and in another procedure is called by this SP (e.g. in trigger on the table you update).

So you probably rollback not the transaction you started within your sp.

_____________
Code for TallyGenerator

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

A ROLLBACK will always do two things - it will undo all work done from the outermost BEGIN TRANSACTION and it will reset @@TRANCOUNT to 0 (if you use savepoints it may be a little different, but these are rare situations, so I ignore savepoints here for the sake of argument)

There's often some confusion about how BEGIN TRAN, COMMIT TRAN and @@TRANCOUNT works. It's crucial for any programmer to have full understanding of these in order to write robust code.

Very simple we can say that you cannot nest transactions in any other way than syntax.

example:

BEGIN TRAN -- increments @@TRANCOUNT +1 (now 1)

.. do some stuff

BEGIN TRAN -- increments @@TRANCOUNT +1 (now 2)

.. do some more stuff

COMMIT -- decrements @@TRANCOUNT -1 (now 1 again)

-- does NOT commit anything at this point!

-- A commit only happens when @@TRANCOUNT = 0

.. do even more stuff

COMMIT -- decrements @@TRANCOUNT -1 (now 0 again)

-- Now we have commited the transaction.

So... you only COMMIT when @@TRANCOUNT = 0.

If @@TRANCOUNT > 1 and you say COMMIT, all that happens is that @@TRANCOUNT is decremented by 1, nothing else.

But... if you somewhere say ROLLBACK, @@TRANCOUNT is immediately reset to zero, and work is rollbacked to

the outermost (first) BEGIN TRAN. From that point, your code may continue executing (depending on what kind of error was encountered)

In any case, it's critical to have an understanding about these things when you design and code the flow of your transactions.

/Kenneth

Philip Yale-193937 SSCarpal Tunnel Points: 4603 More actions · Answer 4

Serqiy,

Craig's got it all covered here. By issuing a ROLLBACK, he ensures that the entire current transaction is rolled back (as Kenneth says, not just the current nesting level), which is surely what you need to do. If part of a transaction has failed why would you not want to rollback the entire thing, even if it's spread over several nested stored procedures? Craig's template ensures that he always RETURNs from the code to prevent any further execution of statements within that stored procedure. (This is vital; you've now terminated the transaction, but if you don't also stop the code execution you'll continue to issue DML statements that were intended to be part of a larger, single transaction. Now, though, they'll be individual, non-transactional statements, which could be catastrophic).

The only part of the template design that I'd question is the absence of a suitable error message explaining exactly where the error has occurred and why. This is difficult to implement with a centralised errorhandler, since all errors lead to the same place. Although it's more cumbersome, I personally prefer individual errorhandling around every DML statement, each with its own rollback, error message and return statement.