CODEWITHSUNDEEP
sql-serversql-server-2008
user2135970
user2135970

Reputation: 815

@@ERROR Always Equals Zero

I have the following code:

BEGIN TRY
   EXEC (@action_sql);
END TRY
BEGIN CATCH
   SELECT @change_id AS ChangeId,
          @action_sql AS sqlAttempted,
          ERROR_MESSAGE () AS ErrorMessage,
          ERROR_NUMBER () AS ErrorNumber,
          ERROR_SEVERITY () AS ErrorSeverity,
          ERROR_LINE () AS ErrorLine;
END CATCH;

IF @@ERROR = 0
   BEGIN
      PRINT '@@ERROR = ' + CAST (@@ERROR AS VARCHAR (10))
      PRINT 'COMMIT';
      COMMIT TRANSACTION;
   END
ELSE
   BEGIN
      PRINT '@@ERROR = ' + CAST (@@ERROR AS VARCHAR (10))
     PRINT 'ROLLBACK';
     ROLLBACK TRANSACTION;
   END;

When I put a valid or invalid SQL statement in @action_sql I always get printed

@@ERROR = 0
COMMIT

@@ERROR always returns zero even if action_sql is invalid.

What is the easiest way to determine if some sort of error took place?

Thanks,

Upvotes: 0

Views: 3081

Answers (2)

M.Ali
M.Ali

Reputation: 69554

The @@ERROR function is populated as soon as an error occurs and if there is any other statement executing after this function its values is reset to NULL so you have to use a variable to capture the value of @@ERROR function after you have executed your statement,

So to anticipate where an error can occur and then Store its value to a Variable is really an over kill when you have TRY..CATCH blocks.

Since you are using TRY..CATCH blocks, you really dont need to use @@ERROR function. just change the order of your code a little bit.

something like this...

BEGIN TRY
 BEGIN TRANSACTION

   EXEC (@action_sql); --<-- if an error occurs here control jumps to catch block and
                          -- following lines of code never gets executed. (I mean in case of an error)
   PRINT 'COMMIT';        
   COMMIT TRANSACTION;
END TRY

BEGIN CATCH
 IF @@TRANCOUNT <> 0      --<-- Once an error occurred check for any open trans
   ROLLBACK TRANSACTION      -- if there is one ROLLBACK it.

   SELECT @change_id AS ChangeId,         --<-- Rest of the ERROR Functions 
          @action_sql AS sqlAttempted,
          ERROR_MESSAGE () AS ErrorMessage,
          ERROR_NUMBER () AS ErrorNumber,
          ERROR_SEVERITY () AS ErrorSeverity,
          ERROR_LINE () AS ErrorLine;
END CATCH;

Now since you have BEGIN an Explicit Transaction in your TRY block if any error occurs the executing will stop there and the control will jump to CATCH block.

Then there you can check for any OPEN TRANSACTIONs using @@TRANCOUNT and if open just roll back it and get all the other info about the error using ERROR MESSAGES.

Upvotes: 2

Shiva
Shiva

Reputation: 20955

It's because the value for @@ERROR is getting reset to 0 i.e. no error after you've captured and handled it in your TRY CATCH block. So the right way to do this is to capture and store the error values (if any) in variables, and then use those variables to take further action based on whether or not the variables indicate that an error happened.

See sample code below. The @action_sql is invalid in 1st case, and you will see the error code printed out. In the second case, the @action_sql is valid and you won't see any error code printed out.

1st Sample: Invalid @action_sql.

declare @action_sql NVARCHAR(2000);
declare @change_id INT
declare @ErrorNumber int
declare @ErrorMessage  NVARCHAR(2000);

set @action_sql  = 'select * from sysobj;'

BEGIN TRY
   EXEC (@action_sql);
END TRY
BEGIN CATCH    
          SELECT 
          @ErrorMessage =  ERROR_MESSAGE(),
          @ErrorNumber = ERROR_NUMBER()
          -- ERROR_SEVERITY () AS ErrorSeverity,
          -- ERROR_LINE () AS ErrorLine;
END CATCH;

IF @ErrorNumber = 0
   BEGIN
      PRINT '@@ERROR = ' + CAST (@ErrorNumber AS VARCHAR (10))
      PRINT 'COMMIT';
      --COMMIT TRANSACTION;
   END
ELSE
   IF (@ErrorNumber > 0)
   BEGIN
      PRINT '@@ERROR = ' + CAST (@ErrorNumber AS VARCHAR (10))
     PRINT 'ROLLBACK';
     --ROLLBACK TRANSACTION;
   END;

2nd Sample: Valid @action_sql

declare @action_sql NVARCHAR(2000);
declare @change_id INT
declare @ErrorNumber int
declare @ErrorMessage  NVARCHAR(2000);

set @action_sql  = 'select top 10 * from sysobjects;'

BEGIN TRY
   EXEC (@action_sql);
END TRY
BEGIN CATCH    
          SELECT 
          @ErrorMessage =  ERROR_MESSAGE(),
          @ErrorNumber = ERROR_NUMBER()
          -- ERROR_SEVERITY () AS ErrorSeverity,
          -- ERROR_LINE () AS ErrorLine;
END CATCH;

IF @ErrorNumber = 0
   BEGIN
      PRINT '@@ERROR = ' + CAST (@ErrorNumber AS VARCHAR (10))
      PRINT 'COMMIT';
      --COMMIT TRANSACTION;
   END
ELSE
   IF (@ErrorNumber > 0)
   BEGIN
      PRINT '@@ERROR = ' + CAST (@ErrorNumber AS VARCHAR (10))
     PRINT 'ROLLBACK';
     --ROLLBACK TRANSACTION;
   END;

Upvotes: 5

Related Questions