HOTSPOT
You have the following stored procedure:
You run the following Transact-SQL statements:
What is the result of each Transact-SQL statement? To answer, select the appropriate options in the answer
area.
Hot Area:
Answer: 
Explanation:
Box 1: All transactions are rolled back.
The first IF-statement, IF @CODE = ‘C2323’ AND @ApplicationID = 1, will be true, an error will be raised, the
error will be caught in the CATCH block, and the only transaction that has been started will be rolled back.
Box 2: Only Log1, Log2, and Log3 tables are updated.
The second IF-statement, IF @Code = ‘C2323’, will be true, so the second transaction will be rolled back, but
log1, log2, and log3 was updated before the second transaction.
The code looks strange a lot of mistakes but if you keep the begin tran with rollback tran inside the try then both answers are all transactions are rolled back. as it is there right now the answers are wrong
The given code in the example is rubbish.
The second “BEGIN TRAN” seems to be wrong. If it stays the transaction would be rolled back before any insert was done, or the insert into Log4 would be committed. But the there is no commit for the first transaction.
If the second “BEGIN TRAN” is removed – which at least would take care that the first transaction could be committed or rolled back in any case then the second execution would also log no record.
Please find below example code which contains the second “BEGIN TRAN” it doesn’t cause syntax issues but the result is still that in both cases nothing is logged. I slightly amended the code to show that we run into the roll back in the second execution.
So from this perspective in both cases the right answer is “All transactions are rolled back.”
IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘Log1’)
BEGIN
DROP TABLE dbo.Log1;
END;
IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘Log2’)
BEGIN
DROP TABLE dbo.Log2;
END;
IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘Log3’)
BEGIN
DROP TABLE dbo.Log3;
END;
IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘Log4’)
BEGIN
DROP TABLE dbo.Log4;
END;
CREATE TABLE Log1 (Code CHAR(5), ApplcationId INT, Info VARCHAR(1000));
CREATE TABLE Log2 (Code CHAR(5), ApplcationId INT, Info VARCHAR(1000));
CREATE TABLE Log3 (Code CHAR(5), ApplcationId INT, Info VARCHAR(1000));
CREATE TABLE Log4 (Code CHAR(5), ApplcationId INT, Info VARCHAR(1000));
GO
CREATE PROC dbo.UpdateLogs @Code CHAR(5), @ApplicationId INT, @Info VARCHAR(1000)
AS
BEGIN
BEGIN TRY
BEGIN TRAN
INSERT INTO dbo.Log1 VALUES (@Code, @ApplicationId, @Info)
IF (@Code = ‘C2323’ AND @ApplicationId = 1)
RAISERROR(‘C2323 code from HR application!’, 16, 1)
ELSE
INSERT INTO dbo.Log2 VALUES (@Code, @ApplicationId, @Info)
INSERT INTO dbo.Log3 VALUES (@Code, @ApplicationId, @Info)
BEGIN TRAN
IF @Code = ‘C2323’
BEGIN
SELECT ‘Rolling back’;
ROLLBACK TRAN
END
ELSE
INSERT INTO dbo.Log4 VALUES (@Code, @ApplicationId, @Info)
IF @@TRANCOUNT > 0
COMMIT TRAN
END TRY
BEGIN CATCH
IF XACT_STATE() != 0
ROLLBACK TRAN
END CATCH
END
GO
EXEC dbo.UpdateLogs ‘C2323’, 1, ‘Employee records are updated.’
SELECT ‘First Execution’;
SELECT * FROM dbo.Log1;
SELECT * FROM dbo.Log2;
SELECT * FROM dbo.Log3;
SELECT * FROM dbo.Log4;
DELETE FROM dbo.Log1;
DELETE FROM dbo.Log2;
DELETE FROM dbo.Log3;
DELETE FROM dbo.Log4;
EXEC dbo.UpdateLogs ‘C2323’, 10, ‘Sales process started.’
SELECT ‘Second Execution’;
SELECT * FROM dbo.Log1;
SELECT * FROM dbo.Log2;
SELECT * FROM dbo.Log3;
SELECT * FROM dbo.Log4;
DROP PROCEDURE dbo.UpdateLogs;