Error handling and propagation of errors to caller

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error handling and propagation of errors to caller

Author

Topic

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-05-14 : 11:18:57

With the new features of SQL Server 2005 for error handling (TRY...CATCH blocks), how are you propagating errors back to the caller? For example, lets say we have 3 stored procedures:
dbo.usp_UpdateSomeTable1
dbo.usp_UpdateSomeTable2
dbo.usp_UpdateSomeTable3

Let say some application calls dbo.usp_UpdateSomeTable1, in turn dbo.usp_UpdateSomeTable1 calls dbo.usp_UpdateSomeTable2 and in turn dbo.usp_UpdateSomeTable2 calls dbo.usp_UpdateSomeTable3.

Now if dbo.usp_UpdateSomeTable3 generates an error, how do you handle propagating this back to the caller?

I envision encapsulating the contents of each procedure in a TRY...CATCH block like so:

BEGIN TRY
...do some stuff
END TRY
BEGIN CATCH
...handle errors - whether generated from our own RAISERROR statements or by the database engine.
END CATCH

Now my problem is I would like to capture all the error variables and toss them back to the caller and keep sending that information up the stack. So far my attempts have been pretty unreadable and end up being just a cluster of text.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-05-14 : 13:13:35

I think I worked something out. Thoughts anyone?

Call with:

exec dbo.usp_test1

Sample script:

if object_id('dbo.usp_test1') is not null drop procedure dbo.usp_test1
if object_id('dbo.usp_test2') is not null drop procedure dbo.usp_test2
if object_id('dbo.usp_test3') is not null drop procedure dbo.usp_test3
go

create procedure dbo.usp_test1
as
declare @errorseverity int, @errorstate int, @returncode int;
declare @stacktrace nvarchar(max);
declare @procedure_name sysname; set @procedure_name = OBJECT_NAME(@@PROCID);

begin try
    exec @returncode = dba.dbo.usp_test2
    if (@returncode != 0)
        raiserror('failed to execute procedure ''%s''.', 15, 1, @procedure_name);
end try
begin catch
    set @errorseverity = error_severity();
    set @errorstate = error_state();
    set @stacktrace =
        'Error ' + cast(error_number() as varchar) 
        + ' generated in procedure ' + error_procedure()
        + ' on line ' + cast(error_line() as varchar)
        + ' while executing procedure ' + @procedure_name
        + '.' + char(13) + char(10) + error_message();
    raiserror(@stacktrace, @errorseverity, @errorstate);
end catch
go

create procedure dbo.usp_test2
as
declare @errorseverity int, @errorstate int, @returncode int;
declare @stacktrace nvarchar(max);
declare @procedure_name sysname; set @procedure_name = OBJECT_NAME(@@PROCID);

begin try
    exec @returncode = dba.dbo.usp_test3
    if (@returncode != 0)
        raiserror('failed to execute procedure ''%s''.', 15, 1, @procedure_name);
end try
begin catch
    set @errorseverity = error_severity();
    set @errorstate = error_state();
    set @stacktrace =
        'Error ' + cast(error_number() as varchar) 
        + ' generated in procedure ' + error_procedure()
        + ' on line ' + cast(error_line() as varchar)
        + ' while executing procedure ' + @procedure_name
        + '.' + char(13) + char(10) + error_message();
    raiserror(@stacktrace, @errorseverity, @errorstate);
end catch
go

create procedure dbo.usp_test3
as
declare @errorseverity int, @errorstate int, @returncode int;
declare @stacktrace nvarchar(max);
declare @procedure_name sysname; set @procedure_name = OBJECT_NAME(@@PROCID);

begin try
    raiserror('this is a test error', 15, 1)
end try
begin catch
    set @errorseverity = error_severity();
    set @errorstate = error_state();
    set @stacktrace =
        'Error ' + cast(error_number() as varchar) 
        + ' generated in procedure ' + error_procedure()
        + ' on line ' + cast(error_line() as varchar)
        + ' while executing procedure ' + @procedure_name
        + '.' + char(13) + char(10) + error_message();
    raiserror(@stacktrace, @errorseverity, @errorstate);
end catch
go

Generates:

Msg 50000, Level 15, State 1, Procedure usp_test1, Line 22
Error 50000 generated in procedure usp_test2 on line 22 while executing procedure usp_test1.
Error 50000 generated in procedure usp_test3 on line 20 while executing procedure usp_test2.
Error 50000 generated in procedure usp_test3 on line 9 while executing procedure usp_test3.
this is a test error

I think this works fine for producing a readable callstack back to the caller.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources