Exam 70-465 topic 1 question 32 discussion

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.
Select and Place:

Suggested Answer:
Note:
* Read Committed is SQL Server's default isolation level.
* @@ROWCOUNT
eturns the number of rows affected by the last statement.
* Using TRYCATCH in a transaction
The following example shows how a TRYCATCH 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 -
Reference:
http://msdn.microsoft.com/en-us/library/ms175976.aspx