Exceptional Handling in Store Procedures [Resolved]

Posted by Rajendra.prasad under Sql Server on 3/22/2010 | Views : 1026 | Status : [Member] | Replies : 5
Hi All,
how to handle exceptions in store procedures.

Regards,
Rajendra




Responses

Posted by: Pandians on: 3/22/2010 [Member] [MVP] Silver

Up
0
Down

Resolved
Hi Rajendra.prasad

You have two methods :

1. @@Error
2. Try...Catch


1. @@Error :
If there is any Err, The Global System Function @@Error will be > 0

2. Try...Catch
Begin Try
--- Actual Operation goes here
End Try
Begin Catch
--- Capturing the exception using Error Functions.
End Catch

Cheers




Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakhangarg on: 3/22/2010 [Member] [Moderator] Silver

Up
0
Down
Hi Rajendra-

You can handle the error with the following code:

IF(@ERROR>0)
BEGIN
-- Write your Query to Handle Error
END

You can also use Transaction to handle error.

Thanks
Lakhan Pal Garg

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajendra.prasad on: 3/22/2010 [Member] Starter

Up
0
Down
Hi Lakhan,
thnx 4ur quick reply. :)
is it possible to have/write try and catch blocks in stored procedures?

Regards,
Rajendra

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajendra.prasad on: 3/22/2010 [Member] Starter

Up
0
Down
Pandians,
thnx a ton.

can u even elaborate further on @@Error?

Raj

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sagarp on: 3/23/2010 [Member] Bronze

Up
0
Down
BEGIN TRY
BEGIN TRY
EXECUTE usp_ExampleProc
END TRY

BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity;
END CATCH;

EXECUTE usp_ExampleProc
END TRY

BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity;
END CATCH;

Thanks
SagarP
http://www.emanonsolutions.net
http://emanonsolutions.blogspot.com/

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response