DRAG DROP
You are planning to deploy a database to Windows Azure SQL Database.
You need to design a stored procedure to update rows. The stored procedure must meet the
following requirements:
• If the update fails, an error must be raised to the application and the update must be discarded.
• The stored procedure must be designed to maximize concurrency.
What should you include in your design?
To answer, move the appropriate actions from the list of actions to the answer area and
arrange them in the correct order.
Answer: See the explanation
Explanation:
Box 1: Begin an explicit transaction.
Box 2: Perform an update in a try block.
Box 3: Read the @@ROWCOUNT system variable.
Box 4: Raise an error and roll back the transaction if the row count is less than 1.
Box 5: Commit the transaction in a finally block.Note:
* Read Committed is SQL Server’s default isolation level.
* @@ROWCOUNT
eturns the number of rows affected by the last statement.
* Using TRY…CATCH in a transaction
The following example shows how a TRY…CATCH block works inside a transaction. The
statement inside the TRY block generates a constraint violation error.
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;
GO
There is no Finally block in TSql. This question is either incomplete or just plain wrong.
Agree!
This looks like a bastardised version of a different question.
With the way that this question is put what’s wrong with the following?
1) Perform the update in a try block
2) Raise an error in a catch block
There’s no mention that there is anything other than a single update statement happening. This single update statement will have its own implicit transaction. If it fails it fails right? There is no mention of the requirement for the update having affected at least one row.
begin try
begin transaction
set transaction isolation level serializable;
— perform update
— need to check the value of @@error after every update
— otherwise its value will be overwritten by the new update statement
if @@error > 0 raiseerror()
commit transaction
end try
begin catch
rollback transaction
end catch
maximize concurrency =/= serializable
1. Begin explicit
2. Update in try
3. Raise erron in catch
4. Commit transaction