Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 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
Introduction

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.

Description:
  
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


BEGIN TRY
---Write Your Code
END TRY
BEGIN CATCH
---Write Code to handle errors
END CATCH
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


BEGIN TRY
SELECT 300/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
If we run above query we will get output like as shown below

Output


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

Get the latest Asp.net, C#.net, 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

20 comments :

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...

vgfgg

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
{
...
};

TableA.InsertAllOnSubmit(toInsert);
dc.SubmitChanges();


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 back....vsateesh.mca@gmail.com

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
try
{.....
}
catch
{
.....
error msg
}


in
c# using ado

want to print error msg of catch block of sql server

my email id :meh.oswal@gmail.com
thanks...

Unknown said...

hi sir,
I need your help

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

stored proc
try
{.....
}
catch
{
.....
error msg
}


in
c# using ado

want to print error msg of catch block of sql server

my email id :rathorajay202@gmail.com
thanks...

Abdul Razack said...

good explanation sir.

Unknown said...

" how to ONLY INSERT the multiple records into sqlserver database table in one go. "

u can do it using userdefined table type.

Unknown said...
This comment has been removed by a blog administrator.
Unknown said...
This comment has been removed by the author.
Unknown said...

hi sir,
I need your help

BEGIN TRY
use ASB
print 'No errors'
end try
begin catch
print 'Error occured'
end catch


Error message displayed on below message

Msg 911, Level 16, State 1, Line 2
Database 'ASB' does not exist. Make sure that the name is entered correctly.

This didnt go catch block.can you tell me any other passibilities

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

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