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

Hi

Seems ok, you may want to check @@ERROR after the commit.

John

Show quoteHide quote

You may want to change the order:

if (@retvalue!=0) return @retvalue -- error returned from SP
else if (@err!=0) return @err -- error
else return -999 -- incorrect rowcount

Otherwise, you may lose the value of @retvalue if a rollback occurs within
the executed stored procedure

Show quoteHide quote

On Sat, 10 Sep 2005 07:51:02 -0700, Craig HB wrote:

Hi Craig,

The upside of templates such as this is that they minimize the chance of
programmers forgetting to include stuff such as error handling, or
getting it wrong, or wasting time re-inventing the wheel.

The downside is that they might stop to think. Some stored procedures do
not need to enclose the commands in a transaction. Others might even
need multiple transactions. Etc etc.

Remember that there are only three relevant values: 0, 1 and more. Never
include tests that cause a rollback if "more than 100" (or some other
magic number) rows are affected. One day, your database will grow to a
size where more than 100 rows should be expected to be affected, and
then you'll curse yourself for ever writing that check.

<nitpicking>
Finally: use <> to test inequality, not !=. Both work, but <> is
standard and != is not.
</nitpicking>

Best, Hugo

Hi Craig,

The only observation I'd make is that by using a generic errorhandler code
block (goto errorhandler)  you can't display an errormessage that is tailored
to the message itself  i.e. you've chosen a "1 message fits all" solution. 

I prefer individual error-handling code for each DML statement.  More
cumbersome to write, I know, but when it comes to debugging I think it's
invaluable to have a dedicated message that indicates exactly what error has
happened.

If you simply pass the error number back down the nesting levels until you
reach the outermost level, you have no idea where that error actually
originated - it could have been at any level in any SP.

Hugo:  I'm going to amend your rowcount definition slightly.  You say the
only 3 conditions are 0, 1 and more.  I prefer to look on it as 0, 1 and >1. 
Now, there are times when >1  is most certainly incorrect.  Whilst I agree
that a condition such as ">100"  is too specific, and can easily change over
time,  a condition such as ">1" can usually be guaranteed to be wrong in the
right circumstances. 

For example,  if you are updating what is a supposedly unique record and you
affect >1 rows, you need to know this.  It could mean you've lost a PK
constraint, or a check constraint, and if the errorhandling doesn't alert you
to this fact then you'll be none the wiser.  The subsequent data "corruption"
could then propogate throughout the database, getting progressively worse.

Phil

HowTo:Dynamically Inject a Select Query Into UDF
How to write results of a sp/query to multiple .csv/xls files
What is SQL Server version of Oracle's CPU_PER_CALL, or how do I restrict max. exec. time of a query
SImple code error
How Do I Avoid Update Deadlock