Error Handling

System Errors	Custom Errors
it can be viewed in the sys.messages system-view and are defined by SQL Server.	to add a custom error message to thesys.messagesstored proceduresp_addmessageis used.
	they are generated by T-SQL custom code based on your code or business logic.

EXEC sp_addmessage message_id, severity,
N'error_message'
GO;

message_id is of integer type and ranges from 50,001 to 2,147,483,647. severity is of smallint type and ranges from 1 through 25.

Raising Error with RAISERROR

RAISERROR allows SQL server to generate an error that could be caught by the calling process.

RAISERROR ( { msg_id | msg_str | @local_variable }  
    { ,severity ,state }  
    [ ,argument [ ,...n ] ] )  
    [ WITH option [ ,...n ] ]

Raising Error with THROW

THROW [ { error_number | @local_variable },  
        { message | @local_variable },  
        { state | @local_variable } ]   
[ ; ]

@@ERROR

@@ERROR is a system variable that holds the error number of the last error occured.

• Disadvantage

  • the value that @@ERROR holds resets as a new error gets generated.

SELECT @@ERROR;

When working with @@ERROR, it is recommended to capture the error number in separate a variable as soon as error occurs.

DECLARE @error_value int;

RAISERROR(N'message', 16, 1);
SET @error_value = @@ERROR;

IF @error_value <> 0
    PRINT 'Error=' + CAST(@error_value AS VARCHAR(8));

Output

Msg 50000, Level 16, State 1, Line 2
Message
Error=50000

TRY...CATCH

BEGIN TRY
     { sql_statement | statement_block }
END TRY
BEGIN CATCH
     { sql_statement | statement_block }
END CATCH