Came up with this over the past couple of days at work. The idea is to cut
down the verbosity of "simple" error handling, while also elegantly handling
nested stored procs, nested transactions, etc.

Our first (important) step was to decide to adopt the convention that the
return value of a stored proc is always an error value, zero if success and
nonzero for error.

OK, given that, the first step is to cut down verbosity using the follow
error handling proc. The tricky part is the use of error and rowcount as
default param values. Unfortunately, we cannot default procId = procId
because it will pick up the id of spCatch, not the id of the calling proc.

Aside: I assume all of this will be much more readable if you paste it
elsewhere and remove the word wrapping that will occur on the newsgroup.
Also, please excuse any errors in the scripts, I am just demonstrating an
idea so I haven't compiled the code fragments (other than spCatch).

---------------------------------------------
create procedure spCatch(
procid int = null,
msg varchar(128) = null,
unwanted int = null,
wanted int = null,
actual int = rowcount,
error int = error,
savepoint varchar(32) = null
)
as
begin
set nocount on

-- if there was no error and we did not get unwanted and we did get
wanted then just return without error
if(error = 0 and isnull(unwanted, actual - 1) <> actual and
isnull(wanted, actual) = actual) return 0

-- otherwise if we have a procid or message raise an error and return a
non zero value
if(procId is not null or msg is not null)
begin
declare longMsg varchar(512)
set longMsg = ''
set longMsg = longMsg + char(10) + char(13) + ' SOURCE = ' +
isnull(object_name(procId), 'unspecified')
set longMsg = longMsg + char(10) + char(13) + ' MESSAGE = ' +
isnull(msg, 'unspecified')
set longMsg = longMsg + char(10) + char(13) + ' ERROR # = ' +
isnull(cast(error as varchar), 'unspecified')
set longMsg = longMsg + char(10) + char(13) + ' WANTED = ' +
isnull(cast(wanted as varchar), 'unspecified')
set longMsg = longMsg + char(10) + char(13) + ' ACTUAL = ' +
isnull(cast(actual as varchar), 'unspecified')
raiserror('%s', 16, 1, longMsg)
end

if(savepoint is not null) rollback tran savepoint

return (
case
when error <> 0 then error
else -1
end
)
end
---------------------------------------------

Its basic usage is quite simple: after any insert/update/delete, call
spCatch and fail if spCatch returns nonzero. Given no arguments, spCatch
will not raise an error message. If the insert in the following example
fails, SQL server will raise an error to the client anyway, so unless you
want to add a second custom message, the following is sufficient:

---------------------------------------------
Insert into T
select 1,2,3

exec r = spCatch
if (r <> 0) goto Exit
---------------------------------------------

Or for single line convenience, readability is still maintained if you are
not passing params...

---------------------------------------------
Insert into T
select 1,2,3

exec r = spCatch if (r <> 0) goto Exit
---------------------------------------------

The advantage in the above case is nonexistant over the standard solution:

---------------------------------------------
Insert into T
select 1,2,3

if(error <> 0) goto Exit
---------------------------------------------

But it does reinforce the consistent error handling.
We start to get real advantages when we know what rowcount should / should
not be and we want to test for that, or if we want to raise a specific
error. Since I am passing more params now, I have split it over two lines
for readability:

---------------------------------------------
Insert into T
select 1,2,3

exec r = spCatch procid, 'insert into T failed', wanted = 1
if (r <> 0) goto Exit
---------------------------------------------

vs our traditional method, which is much more verbose / intrusive:

---------------------------------------------
declare procname sysname
set procname = object_name(procid)

Insert into T
select 1,2,3

if(error <> 0 or rowcount <> 1)
begin
raiserror('%s: insert into T failed', 16, 1, procname)
goto Exit
end
---------------------------------------------

I haven't tested this part yet, but it will also handle savepoints, rolling
back to the passed savepoint if something went wrong:

---------------------------------------------
begin tran
set sp = 'saved'
save tran sp

insert into T
select 1,2,3

exec spCatch wanted = 1, savepoint = sp

commit
---------------------------------------------

You can even use it to perform assertions or to force error messages /
warnings:

---------------------------------------------
declare i int set i = 1
declare j int set j = 2

exec spCatch procid, 'assertion failed: i <> j', wanted = i, actual =
j

exec spCatch msg = 'forced error / warning message', error = 1 -- or even
error = null
---------------------------------------------

Finally, given the convention we adopted, handling an error from a nested
proc is identical to spCatch, but you don't need to call spCatch here
because it is assumed the nested proc handles it's own errors:

---------------------------------------------
exec r = spNestedProc
if(r <> 0) goto Exit
---------------------------------------------

OK, enough with spCatch.
Now, you'll notice that nowhere in the above snippets do I cover rollbacks.
We decided we wanted 2 more things:
1) Balanced transactions: never get either the "commit / rollback has no
begin" error, nor the "transaction count after execute" problem.
2) A single exit point from every stored procedure.
The skeleton (template) we came up with looks like this:

---------------------------------------------
create proc spTemplate
as
begin
set nocount on
declare TC int set TC = trancount
declare r int set r = -1

if(TC = 0) begin tran

set r = 0
Exit:
if(trancount > TC)
begin
if(r = 0) commit else rollback
end
return r
end
---------------------------------------------

For a procedure that is never going to need a transaction, TC and
conditional error handling at the exit label can obviously be cut out.
OK, so let's see what one of our procs might look like with all of this in
place. I have used spCatch in various ways to demonstrate some flexibility:

---------------------------------------------
create procedure spWhatever
as
begin
set nocount on
declare TC int set TC = trancount
declare r int set r = -1

declare T table(c int)

if(TC = 0) begin tran

delete from T

exec r = spCatch msg = 'we shouldnt have anything to delete yet!',
wanted = 0
if(r <> 0) goto Exit

insert into T select 1

exec r = spCatch procid, 'insert failed'
if(r <> 0) goto Exit

exec r = spInnerProc
if(r <> 0) goto Exit

declare n int
select n = count(*) from T

exec r = spCatch procid, 'assertion failed: we should have a row
here!', unwanted = 0, actual = n
if(r <> 0) goto Exit

delete from T

exec r = spCatch procid, 'we didn't delete everything!?', wanted =
n
if(r <> 0) goto Exit

exec spCatch procId, 'note: about to hit the exit point!', error = 1

set r = 0
Exit:
if(trancount > TC)
begin
if(r = 0) commit else rollback
end

return r
end
---------------------------------------------