I have the following code, and I am wondering, as a generic example, if the transaction is left open if it exits with RETURN
.
BEGIN TRANSACTION
BEGIN TRY
IF NOT EXISTS(SELECT 1 FROM dbo.tblProducts WHERE intProductID = @intProductID)
BEGIN
SELECT 'Product does not exists' AS strMessage
RETURN
END
UPDATE dbo.tblProducts SET
curPrice = 10
WHERE
intProductID = @intProductID
SELECT 'Success' AS strMessage
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS strMessage
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
Option A is the correct choice. It is possible for all statements in a transaction to work and then the actual COMMIT to fail, so you keep the COMMIT inside your TRY block so that any failure of the COMMIT will be caught and you can gracefully handle this error and rollback.
In the TRY block a transaction is started and the two UPDATE statements are performed. If both UPDATEs succeed, the COMMIT will be reached and the transaction committed. If, however, either one produces an error, control will be execute CATCH block where the transaction will be rolled back.
@@TRANCOUNT (Transact-SQL)Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.
If the stored procedure contains a TRY... CATCH construct, the error transfers control to the CATCH block in the stored procedure. When the CATCH block code finishes, control is passed back to the statement immediately after the EXECUTE statement that called the stored procedure.
It should be like Below
BEGIN TRY
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRANSACTION
IF NOT EXISTS(SELECT 1 FROM dbo.tblProducts
WHERE intProductID = @intProductID)
BEGIN
SELECT 'Product does not exists' AS strMessage
Rollback TRan
RETURN
END
UPDATE dbo.tblProducts SET
curPrice = 10
WHERE
intProductID = @intProductID
SELECT 'Success' AS strMessage
COMMIT TRAN
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS strMessage
ROLLBACK TRANSACTION
END CATCH
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With