Hi All,
Here i am writing some words on errors
i hope this will help someone.
All of the system and user-defined error messages in an instance of the Database Engine are contained in the sys.messages
The RAISERROR statement can be used to return user-defined errors to an application.
To Implement error handling for Transact-SQL we can use Try and Catch , same as we do in our C# application
Syntax :
BEGIN TRY
Sql statement goes here
END TRY
BEGIN CATCH
Sql statement goes here
END CATCH
• Try-Catch handles all exceptions with severity greater than 10 that do not terminate the database connection
• A Catch block must be there after Try Block
• If we will write anything between END TRY and BAGIN CATCH, it will surely raise an error.
• GoTo statements cannot be used to enter a TRY or Catch Block.
• We can not use Try –Catch between user defined function.
How to Retrieve Error:
ERROR_NUMBER(): return error number
ERROR_SEVERITY(): return the severity
ERROR_STATE(): return error state number
ERROR_PROCEDURE: return sp/trigger name
ERROR_LINE():return the line number
ERROR_MESSAGE():return the complete text of the error message.
BEGIN TRY
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
Run this script and see the detail of all above
Severity Level:
0-9 : return status info or report errors that are not severe. Database engine does not raise errors with severities 0 to 9.
10 : return status info or report errors that are not severe. For compatibility database engine converts severity 10 to 0 before returning the error info to the application.
please make me correct
if u find me wrong somewhere
|
| Author: http://venkattechnicalblog.blogspot.com/ 28 May 2008 | Member Level: Diamond Points : 2 |
Please follow posting guidelines before submitting an article.
Regards,
Venkatesan Prabu . J
|
| Author: Mahesh Raj 07 Jun 2008 | Member Level: Gold Points : 1 |
This is very good information,Continue posting such useful articles.
|
| Author: John Fernandez 08 Jun 2008 | Member Level: Gold Points : 1 |
Very well written Article.Thanks for sharing this information.
|