Your company requires a standard code snippet to be inserted at the beginning of all stored procedures meeting the following requirements:
• Stored procedures should be able to call other stored procedures
• All stored procedures must incorporate transactions
Here is the core part of the snippet
DECLARE CHAR(36) @TransName = newID()
IF <insert answer here>
Save Transaction @TranName
ELSE
Begin Transaction
A. @@DBTS = 0
B. @@FETCH_STATUS <= -1
C. @@NESTLEVEL = 0
D. @@TRANCOUNT > 1
Correct Answer: D
Explanation/Reference:
SQL Server 2008 error handling best practice
CREATE PROCEDURE SaveTranExample
@InputCandidateID INT
AS
-- Detect whether the procedure was called from an active transaction and save that for later use.
-- In the procedure, @hasOuterTransaction = 0 means there was no active transaction
-- and the procedure started one.
-- @hasOuterTransaction > 0 means an active transaction was started before the -- procedure was called.
DECLARE @hasOuterTransaction BIT = CASE WHEN @@TRANCOUNT > 0 THEN 1 ELSE 0 END;
-- Save points need unique names if modules can nest otherwise you can rollback
-- to the wrong save point. The solution is to use a GUID to name the save points.
DECLARE @rollbackPoint nchar(32) = REPLACE(CONVERT(NCHAR(36), NEWID()), N’-’, N”);
IF @hasOuterTransaction > 0
BEGIN
-- Procedure called when there is an active transaction.
-- Create a savepoint to be able to roll back only the work done in the procedure if there is an error.
SAVE TRANSACTION @rollbackPoint;
END
ELSE
-- Procedure must start its own transaction.
BEGIN TRANSACTION @rollbackPoint;
-- Modify database.
BEGIN TRY
-- Do work;
DELETE HumanResources.JobCandidate
WHERE JobCandidateID = @InputCandidateID;
-- Get here if no errors; must commit
-- any transaction started in the -- procedure, but not commit a transaction
-- started before the transaction was called.
IF @hasOuterTransaction = 0
BEGIN
-- @hasOuterTransaction = 0 means no transaction was started before the procedure was called.
-- The procedure must commit the transaction it started.
COMMIT TRANSACTION;
END
END TRY
BEGIN CATCH
-- An error occurred;
-- If the transaction is still valid
IF XACT_STATE() = 1
-- The XACT_STATE function can return the following values:
-- 1 An open transaction exists that can be either committed or rolled back.
-- 0 There is no open transaction.
-- -1 An open transaction exists, but it is in a doomed state. Due to the type of error that was raised, the transaction can only be rolled back.
BEGIN
-- Because the syntax for ROLLBACK TRANSACTION is the same for the transaction and for a savepoint
-- (ROLLBACK TRANSACTION [ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ])
-- we can write the following:
ROLLBACK TRANSACTION @rollbackPoint;
-- In case @rollbackPoint has the name of a transaction, roll back to the beginning of the transaction.
-- In case @rollbackPoint has the name of a savepoint, roll back to the savepoint.
END;
ELSE IF XACT_STATE() = -1
BEGIN
IF @hasOuterTransaction = 0
BEGIN
-- Transaction started in procedure.
-- Roll back complete transaction.
ROLLBACK TRANSACTION;
END
-- If the transaction is uncommitable, a rollback to the savepoint is not allowed
-- because the savepoint rollback writes to the log. Just return to the caller, which -- should roll back the outer transaction.
END
-- Execute Standard module error handler;
-- After the appropriate rollback, echo error information to the caller.
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE();
SELECT @ErrorSeverity = ERROR_SEVERITY();
SELECT @ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
RETURN -ERROR_NUMBER();
END CATCH
GO
Explanation/Reference:
SQL Server 2008 error handling best practice
CREATE PROCEDURE SaveTranExample
@InputCandidateID INT
AS
-- Detect whether the procedure was called from an active transaction and save that for later use.
-- In the procedure, @hasOuterTransaction = 0 means there was no active transaction
-- and the procedure started one.
-- @hasOuterTransaction > 0 means an active transaction was started before the -- procedure was called.
DECLARE @hasOuterTransaction BIT = CASE WHEN @@TRANCOUNT > 0 THEN 1 ELSE 0 END;
-- Save points need unique names if modules can nest otherwise you can rollback
-- to the wrong save point. The solution is to use a GUID to name the save points.
DECLARE @rollbackPoint nchar(32) = REPLACE(CONVERT(NCHAR(36), NEWID()), N’-’, N”);
IF @hasOuterTransaction > 0
BEGIN
-- Procedure called when there is an active transaction.
-- Create a savepoint to be able to roll back only the work done in the procedure if there is an error.
SAVE TRANSACTION @rollbackPoint;
END
ELSE
-- Procedure must start its own transaction.
BEGIN TRANSACTION @rollbackPoint;
-- Modify database.
BEGIN TRY
-- Do work;
DELETE HumanResources.JobCandidate
WHERE JobCandidateID = @InputCandidateID;
-- Get here if no errors; must commit
-- any transaction started in the -- procedure, but not commit a transaction
-- started before the transaction was called.
IF @hasOuterTransaction = 0
BEGIN
-- @hasOuterTransaction = 0 means no transaction was started before the procedure was called.
-- The procedure must commit the transaction it started.
COMMIT TRANSACTION;
END
END TRY
BEGIN CATCH
-- An error occurred;
-- If the transaction is still valid
IF XACT_STATE() = 1
-- The XACT_STATE function can return the following values:
-- 1 An open transaction exists that can be either committed or rolled back.
-- 0 There is no open transaction.
-- -1 An open transaction exists, but it is in a doomed state. Due to the type of error that was raised, the transaction can only be rolled back.
BEGIN
-- Because the syntax for ROLLBACK TRANSACTION is the same for the transaction and for a savepoint
-- (ROLLBACK TRANSACTION [ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ])
-- we can write the following:
ROLLBACK TRANSACTION @rollbackPoint;
-- In case @rollbackPoint has the name of a transaction, roll back to the beginning of the transaction.
-- In case @rollbackPoint has the name of a savepoint, roll back to the savepoint.
END;
ELSE IF XACT_STATE() = -1
BEGIN
IF @hasOuterTransaction = 0
BEGIN
-- Transaction started in procedure.
-- Roll back complete transaction.
ROLLBACK TRANSACTION;
END
-- If the transaction is uncommitable, a rollback to the savepoint is not allowed
-- because the savepoint rollback writes to the log. Just return to the caller, which -- should roll back the outer transaction.
END
-- Execute Standard module error handler;
-- After the appropriate rollback, echo error information to the caller.
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE();
SELECT @ErrorSeverity = ERROR_SEVERITY();
SELECT @ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
RETURN -ERROR_NUMBER();
END CATCH
GO
