The following system functions are available in the CATCH block and can be used to determine additional error information:
ERROR_NUMBER() Returns the number of the error
ERROR_SEVERITY() Returns the severity
ERROR_STATE() Returns the error state number
ERROR_PROCEDURE() Returns the name of the stored procedure or trigger where the error occurred
ERROR_LINE() Returns the line number inside the routine that caused the error
ERROR_MESSAGE() Returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times
Example 1:
CREATE PROCEDURE DeleteEmployee ( @EmployeeID int )
AS
BEGIN TRY
BEGIN TRANSACTION -- Start the transaction
-- Delete the Employee's phone numbers
DELETE FROM EmployeePhoneNumbers WHERE EmployeeID = @EmployeeID
-- Delete the Employee record
DELETE FROM Employees WHERE EmployeeID = @EmployeeID
-- If we reach here, success!
COMMIT
END TRY
BEGIN CATCH
-- Whoops, there was an error
IF @@TRANCOUNT > 0
ROLLBACK
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
--------------------------------------------------------------http://www.databasejournal.com/features/mssql/article.php/3587891
-----------------------------------------------------------------
Example 2:
USE AdventureWorks;
GO
-- Verify that the stored procedure does not already exist.
IF OBJECT_ID ( 'usp_GetErrorInfo', '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_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
GO
BEGIN TRY
-- Generate divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;
END CATCH;
Example 3:
Verify that the stored procedure does not exist.
IF OBJECT_ID ( 'usp_ExampleProc', 'P' ) IS NOT NULL
DROP PROCEDURE usp_ExampleProc;
GO
-- Create a stored procedure that will cause an
-- object resolution error.
CREATE PROCEDURE usp_ExampleProc
AS
SELECT * FROM NonexistentTable;
GO
BEGIN TRY
EXECUTE usp_ExampleProc
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
END CATCH;
Example 4:
BEGIN TRY
-- Generate a divide-by-zero error.
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;
Example 5:
BEGIN TRANSACTION;
BEGIN TRY
-- Generate a constraint violation error.
DELETE FROM Production.Product WHERE ProductID = 980;
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;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
Example 6:
Check to see whether this stored procedure exists.
IF OBJECT_ID ('usp_GetErrorInfo', '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 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
PRINT 'The transaction is in an uncommittable state. Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test whether the transaction is committable.
IF (XACT_STATE()) = 1
BEGIN
PRINT 'The transaction is committable. Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
No comments:
Post a Comment