Your Prod database resides on an instance of SQL Server 2008. The Item table contains details for each item sold by your company. The Item table has an ItemNo column that is defined as the table’s primary key.
You execute the following Transact-SQL:
CREATE PROCEDURE DeleteItem(@ItemNo int)AS
SAVE TRANSACTION ProcSave1;
BEGIN TRANSACTION;
BEGIN TRY
DELETE FROM Item WHERE ItemNo = @ItemNo;
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0 COMMIT TRANSACTION;
What is the result?
A. If the item passed as input to the procedure exists, it will be successfully deleted.
B. If the item passed as input does not exist, an error message will be displayed.
C. The code generates an error because you cannot use a ROLLBACK TRANSACTION statement within a CATCH block.
D. The code generates an error because you cannot create a savepoint outside a transaction.
Correct Answer: D
Explanation/Reference:
You can only create a savepoint within an active transaction. If you attempt to create a savepoint outside a transaction, the following error will occur:
Msg 628, Level 16, State 0, Procedure DeleteItem, Line 4Cannot issue SAVE TRANSACTION when there is no active transaction.
Savepoints allow you to roll back portions of transactions as needed. To create a savepoint to which you can roll back, you use the SAVE TRANSACTION statement and specify a savepoint name. Then, when you issue a ROLLBACK statement, you can specify the savepoint to which you want to roll back. You can define multiple savepoints within a single transaction.
Because the code fails, the stored procedure is not created. Therefore, the options that state it executes are both incorrect.
The code does not generate an error because you cannot use a ROLLBACK TRANSACTION statement within a CATCH block. You can include a ROLLBACK TRANSACTION statement, with or without a specified savepoint, within a CATCH block. With a TRY…CATCH construct, statements that might generate an error are included in the TRY block, and exception-handling code is included in the CATCH block. If all the statements in the TRY block execute successfully with no errors, execution resumes immediately following the CATCH block. If an error occurs within the TRY block, then the CATCH block is executed. With a TRY…CATCH construct, you can also use built-in functions, such as ERROR_MESSAGE and ERROR_SEVERITY, within the CATCH block to return details about the error that occurred. Or, you can use the RAISERROR statement to return a specific custom error.
Suppose you used the following Transact-SQL to create a stored procedure:
CREATE PROCEDURE DeleteItem2(@ItemNo int)AS
BEGIN TRANSACTION;
INSERT INTO Item(ItemNo, Description)VALUES (4,’Generic Item’);
SAVE TRANSACTION ProcSave1;
BEGIN TRY DELETE FROM Item WHERE ItemNo = @ItemNo;END TRY BEGIN CATCH ROLLBACK TRANSACTION ProcSave1;END CATCH;
COMMIT TRANSACTION;
If you call DeleteItem2 passing it a value that does not exist in the Item table, the first INSERT will insert a row. Then, within the TRY block, the attempt to delete the non-existent item will fail and control will pass to the CATCH block. Within the CATCH block, the ROLLBACK TRANSACTION statement will roll back the transaction to the ProcSave1 savepoint. The insert will still be valid, and the COMMIT TRANSACTION statement will commit the insertion.
If you call the DeleteItem2 stored procedure passing it a value that exists in the Item table, the row will be inserted, the specified row will be deleted, and the CATCH block will not execute.
