aspdotnet-suresh offers articles and tutorials,csharp dot net, articles and tutorials,VB.NET Articles,Gridview articles,code examples of 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Exception Handling in SQL Server Stored Procedure with TRY CATCH

Mar 8, 2013

Here I will explain how to handle exceptions in SQL Server or exception handling in SQL Server for stored procedure by using try catch or error handling in SQL Server.

In previous articles I explained Pass table as parameter to stored procedure in SQL Server, Difference between joins in SQL Server, Convert rows to columns in SQL Server, SQL Query to get duplicate records count and many articles relating to
SQL Server, jQuery, JavaScript. Now I will explain how to handle exceptions in SQL Server.

To handle exceptions in SQL Server we can use TRY…… CATCH blocks. To use TRY…… CATCH blocks in stored procedure we need to write the query like as shown below

---Write Your Code
---Write Code to handle errors
In TRY block we will write our queries and in CATCH block we will write code to handle exceptions. In our SQL statements if any error occurs automatically it will move to CATCH block in that we can handle error messages. To handle error messages we have defined Error Functions in CATCH block those are

ERROR_LINE() - This function will return error line number of SQL query which cause to raise error.

ERROR_NUMBER() - This function will return error number which is unique and assigned to it.

ERROR_SEVERITY() - This function will return severity of error which indicates how serious the error is. The values are between 1 and 25.

ERROR_STATE() - This function will return state number of error message which cause to raise error.

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

ERROR_MESSAGE() - This function will return the complete text of the error message which cause to raise error.

Check below sample query to handle errors in stored procedure

SELECT 300/0
SELECT ErrorNumber = ERROR_NUMBER(), ErrorSeverity = ERROR_SEVERITY(), ErrorState = ERROR_STATE(),
ErrorProcedure = ERROR_PROCEDURE(), ErrorLine = ERROR_LINE(), ErrorMessage = ERROR_MESSAGE()
If we run above query we will get output like as shown below


If you enjoyed this post, please support the blog below. It's FREE!

Get the latest,, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email


Anonymous said...

how to create calendar which specify special events? plz plz help me....

Anonymous said...

Hi Suresh,

this is sunil from delhi..

there is 1 question that
how to ONLY INSERT the multiple records into sqlserver database table in one go.

thnx in advance
waiting for your reply.... :)

Anonymous said...

Hi Suresh,

Ignore my previous post...

this is sunil from delhi..

there is 1 question that
how to ONLY INSERT the multiple records into sqlserver database table in one go using GridView.

thnx in advance
waiting for your reply.... :)

Anonymous said...

Hi suresh sir,
This is J.Prabu, how can i ask my question, kindly give me the details to share my doubt.

Anonymous said...

good one

Anonymous said...

Hello Sir,

Is there any directly PHP to ASP.NET website conversion application??? if yes .. plz send me link for that...
thank you...

Anonymous said...


Kalyan Reddy said...

by using Foreach loop you can insert the Data in One Click in the Database.

Anurag Soni said...

Hello sir,

How we can block my website in other country,like in e-commerce website , international user only can view product bt can't submit order.

gaaba said...

use bulk upload sql in gridview click event

Anonymous said...

This is Abhinav Singh 993 ; Thank you so much Whenever I have been in dilema of thoughts You're guidance is always with me.

Anonymous said...

insert a whole gridview convert the grid view into datatable and then use the datatable select the datatable and then insert it using linq

var toInsert = from b in TableB
where ...
select new A


Regards Andrew

SATEESH said...

Hi Suresh this is fine but i have a problem with date .Actuvally am using datatype is datetime in sql but am using string in code behind file for sending date to database . at that time i want to given wrong date format it will show like "error convert type nvarchar to datetime " .My requirement is to show the error like STRING WAS NOT RECOGNISED AS A VALID DATETIME please retrive me

Anonymous said...

hi sir,
I need your help

I want error trapped in catch block of sql server to be shown in gui

stored proc
error msg

c# using ado

want to print error msg of catch block of sql server

my email id

Give your Valuable Comments

Other Related Posts

© 2010-2012 All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.