Errors

RaiseError

• Help troubleshoot T-SQL code.

• Check the values of data.

• Return messages that contain variable text.

RAISERROR (N'This is msg %s %d.', -- Message text.

           10, -- Severity,

           1, -- State,

           N'number', -- First argument.

           5); -- Second argument.

-- The message text returned is: This is message number 5.

GO

@@ERROR

@@ERROR is a system variable that holds the error number of the last error that has occurred. One significant challenge with @@ERROR is that the value it holds is quickly reset as each additional statement is executed. 

DECLARE @ErrorValue int;

RAISERROR(N'Message', 16, 1);

SET @ErrorValue = @@ERROR;

IF @ErrorValue <> 0

PRINT 'Error=' + CAST(@ErrorValue AS VARCHAR(8));

Output

Msg 50000, Level 16, State 1, Line 2

Message

Error=50000

TRY\CATCH

DECLARE @num varchar(20) = 'A';

BEGIN TRY

    PRINT 5. / CAST(@num AS numeric(10,4));

END TRY

BEGIN CATCH

    EXECUTE dbo.GetErrorInfo;

    IF ERROR_NUMBER() = 8114

    BEGIN

        PRINT 'Handling devision by zero...';

    END

    ELSE 

    BEGIN

        PRINT 'Throwing original error';

        THROW;

    END;

END CATCH;

----====Procedure==GetErrorInfo====----

CREATE PROCEDURE dbo.GetErrorInfo AS

PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS varchar(10));

PRINT 'Error Message: ' + ERROR_MESSAGE();

PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS varchar(10));

PRINT 'Error State: ' + CAST(ERROR_STATE() AS varchar(10));

PRINT 'Error Line: ' + CAST(ERROR_LINE() AS varchar(10));

PRINT 'Error Proc: ' + COALESCE(ERROR_PROCEDURE(), 'Not within procedure');

Output

Error Number: 8114

Error Message: Error converting data type varchar to numeric.

Error Severity: 16

Error State: 5

Error Line: 4

Error Proc: Not within procedure

Handling devision by zero...