The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Template for Stored Procedures

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).


create proc StoredProcedureTemplate
  @Parm1 char(1),
  @Parm2 int

set nocount on

declare @err int
declare @rowcount int
declare @retvalue int

-- assertion
if @Parm1 not in ('A', 'B', 'C')
  raiserror('Parm1 needs to be A, B, C', 16, 1)
  return 50000

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

  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
Craig Hudson-Bennett Send private email
Saturday, September 10, 2005
Interesting, I have done something like this recently and I try to find a balance between ease of reading and complication. If the performance is non-critical I allow nvarchar textual error reporting through an out parameter. And then the local nvarchar variable assigned to report errors will cause a rollback if it's non-zero length (error messages was concatenated to it). For performant stuff I am still playing with a few other ideas.
Li-fan Chen Send private email
Saturday, September 10, 2005
If there is any work that does not have to be in the transcation, such as, gathering data etc., it should go before the begin tran  to reduce the time that the transaction is open. You might want to designate a place for such code. If this is just the update part of the process it is fine.
Sunday, September 11, 2005
You might want to check out CodeSmith and see the templates that others have posted to the forums. No use re-inventing the wheel, as the trite saying goes.
Nick Hebb Send private email
Sunday, September 11, 2005
Using "if (@@trancount!=0) rollback transaction" looks nice, but it can cause nightmares for support when a procedures begins a transaction, calls your procedure, and an error occurs before the begin transaction. At that point the transaction from the calling procedure rolls back. 

That is why I put rollback tran before the raiserror and have nothing transaction related in my error handler.

The number of extra lines code is marginal and you stay in control of your transactions.
lonely consultant
Tuesday, September 13, 2005

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz