CODEWITHSUNDEEP
sqlt-sqlsql-server-2008stored-procedures
gugguson
gugguson

Reputation: 819

Passing error details from catch into log stored procedure

I have a stored procedure in SQL Server with try catch. What I want to do in the catch loop is to call my own Stored procedure for logging with all the error variables, like so:

BEGIN TRY
    -- Generate a divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    exec log.LogError ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_MESSAGE();
END CATCH;

When I run this I get an error on the parenthesis.

I can run:

select ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_MESSAGE();

I can also do

print ERROR_NUMBER()

What I want to do is the have just one line which calles the stored procedure with the parameters because I will have this in many stored procedures and don't want to have lot's of code setting the error parameters (I will have more than these three) in each stored procedure where I have try-catch.

Does anybody know how I can pass these into another stored procedure?

Regards, Johann

Upvotes: 1

Views: 3965

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294227

Unfortunately T-SQL is not a DRY code reuse compact syntax programmer friendly language. You have to do it the hard way, and that implies writing a minimum of 4-5 lines of code inside each CATCH block. Besides, you need to account also for transaction semantics: has it rolled back or not? Or worse, are you in a doomed transaction? That's why I created this T-SQL error handling template:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER()
                   , @message = ERROR_MESSAGE()
                   , @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
        return;
    end catch   
end

Is it longer than what you're looking for? I bet. It is correct? Yes.

And finally, how do you handle logging in a transactional environment? Inserts into a log table will be rolled back along with everything else in case of error. Sometimes that is OK, other times is even desired, but sometimes is problematic. One of the most interesting solutions is Simon Sabin's Logging messages during a transaction.

Upvotes: 2

David Anderson
David Anderson

Reputation: 68

Try changing your log.LogError procedure so it accesses ERROR_NUMBER() and the other error functions directly. There's an example in the documentation.

Upvotes: 1

Related Questions