(adopted from http://sqlmag.com/t-sql/error-handling-sql-server-2005)
SQL Server 2000 and earlier editions is limited and cumbersome. Error-handling code isn't structured and many errors aren't trappable.
Some deadlocks are the result of poor programming or a lack of indexes, but others are intentional. You might design a deadlock to prevent consistency problems, such as lost updates. You'll usually want to handle intentional deadlocks by retrying the transaction. However, because a deadlock is an error that terminates the batch, the retry code never has a chance to run.
Below is adopted from http://www.sommarskog.se/error-handling-I.html and http://www.sommarskog.se/error-handling-II.html)
The current statement is aborted and rolled back. Execution continues on the next statement.
The current scope (stored procedure, user-defined function, or block of loose SQL statements, including dynamic SQL) is aborted, and execution continues on the next statement in the calling scope.
The execution of the entire batch – that is, the block of SQL statements that the client submitted to SQL Server – is aborted.
The client is disconnected and any open transaction is rolled back.
However, if you have trigger context when you are in a trigger, or you are in a stored procedure, user-defined function or block of dynamic SQL that has been called directly or indirectly from a trigger. That is, somewhere on the call stack, there is a trigger. If you are in trigger context, all errors terminate the batch and roll back the transaction on the spot. (Connection-terminating errors still terminate the connection, of course.)
Recommendations:
o ON - Batch-abortion
o OFF - Warning message, result is NULL
o ON - No action at all, result is NULL
o OFF - Warning message, result is NULL
o ON - Statement-termination
o OFF- Return NULL
Why needed? If you catch an error on the server, the client will never see the error, unless you call RAISERROR in the error handler. Unfortunately, you cannot re-raise the exact error message, since RAISERROR does not permit you to use error numbers less than 50000.
No native supported way to retrieve the full text of an error message in SQL 2000. Need to code it such as the one in http://www.sommarskog.se/errhandle/spGET_LastErrorMessage.sp
Microsoft recommends using the TRY...CATCH construct to handle errors. The TRY...CATCH construct also supports additional system functions (ERROR_LINE, ERROR_MESSAGE, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE) that return more error information than @@ERROR. TRY...CATCH also supports an ERROR_NUMBER function that is not limited to returning the error number in the statement immediately after the statement that generated an error.
Six functions have been added and can be used in the CATCH block: error_number(), error_severity(), error_state(), error_procedure() , error_line(), and error_message().
Note: error_message() is the expanded message with the parameters filled in. In SQL 2000. You can only get a text from master.dbo.sysmessages, which is a placeholder for interesting things like which constraint that was violated. To get the full text of the error message in a proper way, you need a client to pick it up and log it.
USE AdventureWorks;
GO
-- Check to see whether this stored procedure exists.
IF OBJECT_ID (N'usp_GetErrorInfo', N'P') IS NOT NULL
DROP PROCEDURE usp_GetErrorInfo;
GO
-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE () AS ErrorLine
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_MESSAGE() AS ErrorMessage;
GO
-- SET XACT_ABORT ON will cause the transaction to be uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- A FOREIGN KEY constraint exists on this table. This
-- statement will generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
-- If the DELETE statement succeeds, commit the transaction.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;
-- Test XACT_STATE:
-- If 1, the transaction is committable.
-- If -1, the transaction is uncommittable and should
-- be rolled back.
-- XACT_STATE = 0 means that there is no transaction and
-- a commit or rollback operation would generate an error.
-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
N'The transaction is in an uncommittable state.' +
'Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test whether the transaction is committable.
IF (XACT_STATE()) = 1
BEGIN
N'The transaction is committable.' +
'Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
GO
(from http://www.codeproject.com/Articles/38650/Overview-of-Error-Handling-in-SQL-Server-2005)
Here I am going to explain one real life scenario of using TRY-CATCH block. One of the common scenarios is usingTransaction. In a Transaction, we can have multiple operations. If all operations executed successfully, then database will commit otherwise we need to ROLLBACK.
/*
I want to delete a Particular Records from Both Student
Details and Library. Database will only commit, iff both
delete statement execute successfully, If fails it will Roll
back. Intentionally I have passed a wrong roll ( Which causes)
the exception and transaction will rollback.
*/
BEGIN TRY
-- Start A Transaction
BEGIN TRANSACTION
-- Delete Student From StudenDetails Table
DELETE FROM StudentDetails WHERE Roll = '1'
Print 'Delete Record from Student Details Table'
-- Delete The Same Student Records From Library Table also
DELETE FROM Library WHERE Roll = 'a'
Print 'Delete Record from Library Table'
-- Commit if Both Success
COMMIT
-- Update Log Details
Insert into LogDetails(ID,Details) values ('1','Transaction Successful');
END TRY
BEGIN CATCH
Print 'Transaction Failed - Will Rollback'
-- Any Error Occurred during Transaction. Rollback
IF @@TRANCOUNT > 0
ROLLBACK -- Roll back
END CATCH
(adopted from http://social.technet.microsoft.com/wiki/contents/articles/20002.structured-error-handling-mechanism-in-sql-server-2012.aspx)
The RAISERROR itself needs other elements to identify the error number, error message, etc as below:
Here is sample code to produce the error details:
SET NOCOUNT ON;
BEGIN TRY -- Start to try executing statements
SELECT 1 / 0; -- Statement
END TRY -- End of trying to execute statements
BEGIN CATCH -- Start to Handle the error if occurs
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH -- End of Handling the error if occurred
Msg 50000, Level 16, State 1, Line 19
Divide by zero error encountered.
THROW [ { error_number | @local_variable },
{ message | @local_variable },
{ state | @local_variable } ]
[ ; ]
-- Example 1: with 3 parameters.
THROW 51000, 'The record does not exist.', 1;
-- Example 2: without parameters.
USE tempdb;
GO
CREATE TABLE dbo.TestRethrow
( ID INT PRIMARY KEY
);
BEGIN TRY
INSERT dbo.TestRethrow(ID) VALUES(1);
-- Force error 2627, Violation of PRIMARY KEY constraint to be raised.
INSERT dbo.TestRethrow(ID) VALUES(1);
END TRY
BEGIN CATCH
PRINT 'In catch block.';
THROW;
END CATCH;
The following table lists differences between the RAISERROR and THROW statements.
RAISERROR statement | THROW statement |
If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages. | The error_number parameter does not have to be defined in sys.messages. |
The msg_str parameter can contain printf formatting styles.
An example of Printf in C:
printf("Color %s, number1 %d, number2 %05d, hex %#x, float %5.2f, unsigned value %u.\n","red", 123456, 89, 255, 3.14159, 250);
An example in SQL Server RAISERROR ('%s %d should be between %u and %d.',16, 1, 'The inventory level for Product', 1, 1, 1000) | The message parameter does not accept printf style formatting. Instead, you use the FORMATMESSAGE function as below: The FORMATMESSAGE function constructs a message from an existing message in sys.messages. The functionality of FORMATMESSAGE resembles that of the RAISERROR statement. However, RAISERROR prints the message immediately, while FORMATMESSAGE returns the formatted message for further processing.
FORMATMESSAGE ( msg_number , [ param_value [ ,...n ] ] ) The following example shows how to use the FORMATMESSAGE function with THROW to throw a customized error message. The example first creates a user-defined error message by usingsp_addmessage. Because the THROW statement does not allow for substitution parameters in the message parameter in the way that RAISERROR does, the FORMATMESSAGE function is used to pass the three parameter values expected by error message 60000.
EXEC sys.sp_addmessage @msgnum = 60000 ,@severity = 16 ,@msgtext = N'This is a test message with one numeric parameter (%d), one string parameter (%s), and another string parameter (%s).' ,@lang = 'us_english'; GO DECLARE @msg NVARCHAR(2048) = FORMATMESSAGE(60000, 500, N'First string', N'second string'); THROW 60000, @msg, 1; Here is the result set. Msg 60000, Level 16, State 1, Line 2 This is a test message with one numeric parameter (500), one string parameter (First string), and another string parameter (second string). |
The severity parameter specifies the severity of the exception. | There is no severity parameter. The exception severity is always set to 16. |
CREATE PROC sptest
AS
SET NOCOUNT ON;
BEGIN TRY
SET XACT_ABORT ON; --set xact_abort option
BEGIN TRAN --begin transaction
CREATE TABLE dbo.T1
( id int );
SELECT 1/0
COMMIT TRAN --commit transaction
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 --check if there are open transaction?
ROLLBACK TRAN; --rollback transaction
THROW
END CATCH
go
EXEC sptest;
go
SELECT *
FROM dbo.T1;