In this tutorial, you will learn Error handling in SQL Server with TRY CATCH blocks in stored procedures.
Error handling in SQL Server give us control over Transact-SQL code. An error condition during a program execution is called an exception and the mechanism for resolving such an exception is known as an exception handler. SQL Server provides TRY, CATCH blocks for exception handling. We can put all T-SQL statements into a TRY BLOCK and the code for exception handling can be put into a CATCH block.
SQL Server TRY CATCH :
To use the TRY CATCH, you first place a group of Transact-SQL statements that could cause an exception in a BEGIN TRY…..END TRY block as follows:
BEGIN TRY -- statements that cause exceptions END TRY
Then you use a BEGIN CATCH…END CATCH block immediately after the TRY block:
BEGIN CATCH -- statements that handle exception END CATCH
The following syntax is complete TRY CATCH block :
BEGIN TRY -- statements that cause exceptions END TRY BEGIN CATCH -- statements that handle exception END CATCH
Anything between the BEGIN TRY and END TRY is the code that we want to monitor for an error. So, if an error would have happened inside this TRY statement, the control would have immediately get transferred to the CATCH statement and then it would have started executing code line by line
Now, inside the CATCH statement, we can try to fix the error, report the error or even log the error so we know when it happened, who did it by logging the username, all the useful stuff. We even have access to some special data only available inside the CATCH statement
The CATCH block functions :
- ERROR_LINE() : returns the line number on which the exception occurred
- ERROR_NUMBER() : returns the number of the error that occurred.
- ERROR_MESSAGE() : returns the complete text of the generated error message.
- ERROR_STATE() : returns the state number of the error that occurred.
- ERROR_PROCEDURE() : returns the name of the stored procedure or trigger where the error occurred.
- ERROR_SEVERITY() : returns the severity level of the error that occurred.
Nested TRY CATCH Block
BEGIN TRY ---the statements that cause exceptions END TRY BEGIN CATCH --the statements That handle exception BEGIN TRY ---the nested TRY block END TRY BEGIN CATCH --- the nested CATCH block END CATCH END CATCH
SQL Server TRY CATCH block example
CREATE PROC sp_logic( @VarA decimal, @VarB decimal, @VarC decimal output ) AS BEGIN BEGIN TRY SET @VarC = @AVar / @VarB; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_MESSAGE() AS ErrorMessage; ,ERROR_STATE() AS ErrorState ,ERROR_LINE() AS ErrorLine END CATCH END; GO
In this stored procedure, we have placed the formula inside the TRY block and called the CATCH block functions ERROR_* inside the CATCH block, if error occurs in Try block then it handled in catch block.
- Auto Refresh Partial View in ASP.NET MVC
- What is ASP.NET Core
- Difference between TempData keep() And Peek() in Asp.Net MVC
- Difference between viewbag,viewdata and tempdata in asp.net mvc
- ASP.NET MVC With AngularJS
- Retrieving Data Using Form Collection and Inserting Into ASP.Net MVC
- MVC CRUD Operations Using Entity Framework
- Search Functionality in ASP.NET MVC
- How to create a User Registration page using asp.net mvc
- Store Multiple Checkbox state from cookie using Jquery
- Cascading Dropdownlist using Ajax in Asp.Net Mvc with city state country
- Insert, Update, Delete In GridView Using ASP.Net C#
- Binding Dropdownlist With Database In Asp.Net MVC
- Search and Filter data in Gridview using Asp.net MVC
- Select Insert, Update And Delete With ASP.NET MVC
- Display Data in GridView Using Asp.net MVC
- Validation in ASP.NET MVC Razor view
- CRUD Operation Using 3-Tier Architecture In ASP.NET
- How to get Connection String from Web.Config in Asp.Net C#
- Login page using 3-Tier Architecture in ASP.Net
- Asp.Net Image Upload in 3-Tier Architecture and store in sql database