CODEWITHSUNDEEP
sql-server-2005
B.M
B.M

Reputation: 563

@@ERROR in SQL Server 2005

I have learned to use the SCOPE_IDENTITY() instead of just @@IDENTITYto get the last identity value inserted in a given scope, which can be quite useful in high-concurrency scenarios. Is there any equivalent to that function for the @@ERROR variable? I mean, is there any way to make sure that whenever I write

IF (@@ERROR <> 0) RETURN

I am in fact forcing the function to return because of the very last command in this scope caused an error?

Upvotes: 3

Views: 10932

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294407

Writing IF (@@ERROR <> 0) after each and every statement is just not going to work. It requires too much discipline. You should move to BEGIN TRY/BEGIN CATCH. Exception handling and nested transactions shows a pattern of T-SQL procedures that handles both exceptions and nested transactions (something to consider in order to make your T-SQL code robust):

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

Also see Error Handling in SQL 2005 and Later for a deeper discussion on this entire topic.

Upvotes: 5

HLGEM
HLGEM

Reputation: 96600

From Books Online:

@@ERROR only returns error information immediately after the Transact-SQL statement that generates the error.

@@Error is only within the current scope. So it should have the value for whatever sent the proc to the catch block no matter which of several statements was the one that errored.

Upvotes: 6

Related Questions