SQL Server: Error Handling
Error handling in SQL Server is similar to C#’s exception handling. Syntax is below:
BEGIN TRY -- this is where you put your statements END TRY BEGIN CATCH -- this is where you handle the error END CATCH
And of course you can get more information about the error using the following:
- ERROR_PROCEDURE() – returns name of stored procedure or trigger where the error occurred
- ERROR_LINE() – returns the line number in the procedure or trigger where the error occurred
- ERROR_NUMBER() – returns the error number
- ERROR_MESSAGE() – returns the error message
The following might also be useful in handling your errors:
- @@TRANCOUNT – holds the # of transactions ongoing; useful if you are using transactions and want to know if you have pending transactions after catching an error (IF @@TRANCOUNT > 0) so you can maybe rollback
- @@PROCID – returns the object ID of the current stored procedure, UDF, or trigger
- OBJECT_NAME() – returns the name of the database object referred to by the object ID; passing @@PROCID will return the name of the current stored procedure, UDF, or trigger (OBJECT_NAME(@@PROCID)
- USER_NAME() – returns database user name (e.g. dbo); for more info go here
- SYSTEM_USER – returns the current Windows or SQL Server login name depending on how the user is logged in; for more info go here