Error Handling in Stored Procedure in MS SQL Server Using Try Catch block and Rise Customize Error with Specific Message using RISEERROR function

Here in this post we will learn basics of how to handle errors in stored procedures in ms sql server. I think we all are familiar with TRY CATCH blocks. SQL Server provides us with an efficient way to handle errors within stored procedures. In this article we will discusses about Error Functions and RAISERROR functions within SQL Server.

Error Functions in Stored Procedure

ERROR_MESSAGE() - returns the error message text which cause to raise error.

ERROR_LINE() - returns error line number of SQL query which raises an error.

ERROR_NUMBER() - returns error number which is unique and assigned to it.

ERROR_SEVERITY() - returns severity of error which indicates how serious the error is. The values are between 1 to 25.

ERROR_STATE() -  return state number of error message.

ERROR_PROCEDURE() - This function will return name of the procedure where an error occurred.

Here in Below Stored Procedure we will use above error function between TRY.... CATCH Block.

Syntax of Try Catch Block in Stored Procedure

BEGIN TRY
        <your specious code>
END TRY

BEGIN CATCH
        <Code To Handle The Error>
END CATCH

BEGIN TRY

               SELECT 8/0

END TRY

BEGIN CATCH

SELECT ErrorNumber = ERROR_NUMBER(), ErrorSeverity = ERROR_SEVERITY(), ErrorState = ERROR_STATE(),

ErrorProcedure = ERROR_PROCEDURE(), ErrorLine = ERROR_LINE(), ErrorMessage = ERROR_MESSAGE()

END CATCH

RAISERROR

This Function is used to customize the error handling and with the use of Raise Error throw a specific error message from stored procedure.

here in this example I want to check  for duplication before inserting record if any record is existed with same user name and password then throw an error.

Begin

If exists (SELECT uname FROM aby_JobApplication WHERE (uname = @un and pass=@pass))

    Begin

            RAISERROR('Duplicate Record',11,12);

    End

Else

    begin

        SELECT * FROM aby_JobApplication where uname=@un 

    end

end

Elements of RAISERROR Function

1. Number - Each error has a unique number assigned to it.
2. Message - Contains information about the error. Many errors have substitution variables that can be placed within the text. We will cover this in the SP_ADDMESSAGE and RAISERROR sections.
3. Severity - Indicates how serious the error is. The values are between 1 and 25.
4. State - Some error codes can be raised at multiple points in the source code for SQL Server. For example, an 1105'error can be raised for several different conditions. Each place the error code is raised assigns a unique state code.
5. Procedure name - If the destruction occurred within a stored procedure, the name is returned.
6. Line - The line number of the demon code.