You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named
dbo.ModifyData that can modify rows. You need to ensure that when the transaction fails, dbo.ModifyData
meets the following requirements:
Does not return an error
Closes all opened transactions
Which Transact-SQL statement should you use?
A.
BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ TRANCOUNT = 0
ROLLBACK TRANSACTION;
END CATCH
B.
BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ERROR != 0
ROLLBACK TRANSACTION;
THROW;
END CATCH
C.
BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT = 0
ROLLBACK TRANSACTION;
THROW;
END CATCH
D.
BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ERROR != 0
ROLLBACK TRANSACTION;
END CATCH
Explanation:
Verified answer as correct.
I think the answer is wrong. B and C are discarded because both of them uses THROW command which returns the original error to the caller. Not A nor D returns the error to the caller, but for D, doesnt make sense to check the @@ERROR because at that point we are inside the CATCH statement so obviously there was an error. Another mistake is to check the @@ERROR variable when we are using a STRUCTURED ERROR HANDLING schema with TRY CATCH statements. The option A seems more CORRECT to me because, while in the CATCH statement, the code checks the value of @@TRANCOUNT before issue the ROLLBACK command to assure there is an ACTIVE transaction. A ROLLBACK without an active transaction will generate an error.
D is correct. It is true that checking @@ERROR wouldn’t be necessary but A checks for “@@TRANCOUNT = 0” and this is complete nonsense.
You can test it on AdventureWorks2012 database (uncomment the 2 commands alternating):
BEGIN TRANSACTION
BEGIN TRY
print cast(@@TRANCOUNT as varchar(12)) + ‘ before update’
update [HumanResources].[Employee] set [VacationHours] = 18/0 where [BusinessEntityID]=12
COMMIT TRANSACTION
print cast(@@TRANCOUNT as varchar(12)) + ‘ after update’
END TRY
BEGIN CATCH
–IF @@ERROR != 0
–IF @@TRANCOUNT = 0
ROLLBACK TRANSACTION;
print cast(@@TRANCOUNT as varchar(12)) + ‘ after rollback’
END CATCH
strange, @@error is 0, it doesn’t trigger the rollback statement.
I would assume there is a typo in the questioning. I would expect @@trancount > 0 rollback;
@@error != 0 which in english means not equal.
For me it would make more sense answer A if this part of the code was equal to 1 “IF @@ TRANCOUNT = 0 “.
The THROW command returns the error to the caller (as Martin said) so B and C are out of the question because “Does not return an error” part.
By exclusion, Answer D it´s the correct one.
Ok check @@ERROR != 0 is unecessary, but D still correct one
If A would check TRANCOUNT = 1, than we will have 2 correct questions
anyway D is correct…
Are coach factory outlet handbags real wholesale nhl jersey Coupo for official coach
factory outlet online store