CODEWITHSUNDEEP
sql-server-2008t-sql
paparazzo
paparazzo

Reputation: 45096

@@ERROR - How to process / evaluate in TSQL?

This update will fail as ID is Identity

But I am getting direct error message.
I am getting none of the PRINT statements.

How do I use @@ERROR?
Using SSMS.
Would you call this a script or a query? 

PRINT   'start';
DECLARE @ErrorVal INT;
UPDATE IndenText SET ID = 7;
SELECT @ErrorVal = @@ERROR;
PRINT @ErrorVal;
IF @ErrorVal <> 0
BEGIN
    PRINT N'A error caught.';
END
PRINT 'done';

This also does not work from me

PRINT   'start';
DECLARE @ErrorVal INT;
BEGIN TRY
    UPDATE IndenText SET ID = 7;
    SELECT @ErrorVal = @@ERROR;
    PRINT @ErrorVal;
END TRY
BEGIN CATCH
    IF @@ERROR <> 0
    BEGIN
        PRINT N'A error caught.' + @@ERROR;
    END
END CATCH
PRINT 'done';

I get
Msg 8102, Level 16, State 1, Line 4 Cannot update identity column 'ID'.

As Martin stated it was a compile error.
Created a constraint that would not be a compile error.
And got the @@ERROR to process.

PRINT   'start';
DECLARE @ErrorVal INT;
DECLARE @newVal INT;
select  @newVal = -1;
BEGIN TRY
    update Twaste1 set ID = @newVal ;
    PRINT 'End Try';
END TRY
BEGIN CATCH
    Select @ErrorVal = @@ERROR;
    PRINT 'Begin Catch';
    IF @ErrorVal <> 0
    BEGIN
        PRINT CAST(@ErrorVal as varchar(30));
        PRINT N'A error caught.';
    END
END CATCH
PRINT 'done'

;

Upvotes: 1

Views: 170

Answers (2)

Moharrer
Moharrer

Reputation: 153

For example For Update Use :

UPDATE HumanResources.EmployeePayHistory
SET PayFrequency = 4
WHERE BusinessEntityID = 1;

IF @@ERROR = 547 PRINT N'A check constraint violation occurred.'; GO

Upvotes: 2

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

I would suggest using TRY... CATCH instead. This will allow you to capture and examine the error thrown.

UPDATE:

I believe Martin already explained why this doesn't work in his comments to the original question, but I'll add a reference to the MSDN TRY... CATCH article:

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

  • Compile errors, such as syntax errors, that prevent a batch from running.
  • Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

Upvotes: 1

Related Questions