Libraries of Stored Procedures and Error Handling

I am trying to design a high quality set of library stored procedures
in MSSQL 2005. My issue at hand is I don't know the best way to
design error handling. For example, suppose I have the following
stack of executed stored procedures in TSQ:

Client (C# .NET)
> PrimaryStoredProcedure (BEGIN/END TRANSACTION)
> LibraryStoredProcedureA
> LibraryStoredProcedureB (RAISERROR ...)

LibraryStoredProcedureB does a RAISERROR and then RETURN. But this
design requires LibraryStoredProcedureA to check for the @@ERROR and
then RETURN, and then PrimaryStoredProcedure to somehow check the
@ERROR (even thought it was cleared) and ROLLBACK.

It just seems like a lot of work. The only good alternative I've come
up with is to wrap each stored procedure in a TRY/CATCH, and then
"rethrow" it back to the caller (even though there doesn't seem to be
any way to rethrow in TSQL). I know you can rethrow by calling
RAISERROR again, as shown at
http://sqlblog.com/blogs/roman_rehak/archive/2007/12/01/how-to-rethrow...rors-in
but for system exceptions I now lose the severity.

If a RAISERROR occurs, ultimately I want the Client to get the
exception.

Is there a better way to implement this than what I've just outlined?

( ) writes:
> It just seems like a lot of work. The only good alternative I've come
> up with is to wrap each stored procedure in a TRY/CATCH, and then
> "rethrow" it back to the caller (even though there doesn't seem to be
> any way to rethrow in TSQL). I know you can rethrow by calling
> RAISERROR again, as shown at
> http://sqlblog.com/blogs/roman_rehak/archive/2007/12/01/how-to-rethrow-
> errors-in-t-sql.aspx
> but for system exceptions I now lose the severity.
>
> If a RAISERROR occurs, ultimately I want the Client to get the
> exception.
>
> Is there a better way to implement this than what I've just outlined?

Go for TRY-CATCH. You can pick up the severity with error_severity
and format that into your error message, which the client has to parse.

Trying to use @@error means a lot of work, and sometimes it does not
work at all.

I don't know if you have read my articles on error handling. They are in
dire need of an update for SQL 2005, but some of the basics may be of
interest to you: http://www.sommarskog.se/error-handling-I.html.


--
Erland Sommarskog, SQL Server MVP, esquel.TakeThisOut@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx