Geek asks and answers

@@ ERROR in SQL Server 2005

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

IF (@@ERROR <> 0) RETURN

Am I really forcing a function to return due to the most recent command in this area caused by an error?

+3
source share
2 answers

From books on the Internet:

@@ ERROR returns only error information immediately after Transact-SQL which generates an error.

@@ . proc catch , .

+6

IF (@@ERROR <> 0) , . . BEGIN TRY/BEGIN CATCH. T-SQL, , ( , T-SQL ):

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

. SQL 2005 .

+5

More articles:


All Articles