Remember those college days when your calculus professor will ask questions such as prove that cosine 90 is zero? No? Ok, take it easy, its not coming back to haunt you anymore. Have you ever thought writing SQL stored procedure with or without Begin End will execute differently? To be frank, I have never thought about it. I started writing SQL queries more than a decade ago and almost every single stored procedure l wrote had a “Begin End”. It is my preferred way even though stored procedures can be written without “Begin End”. This past week l was faced with a challenge when a friend said using “Begin End” in a stored procedure causes it to start a transaction when it runs. I was shocked and started panicking how l have to go back over many stored procedures to remove “Begin End” and it was Friday evening. You know what l mean? I was preparing to go enjoy a nice weekend not worrying myself about “Begin End” causing a deadlock in one of my applications. l always explicitly use “Begin Transaction” clause whenever l need a piece of code to run as a transaction. I didn’t believe my friend’s assertion so l decided to test it out to proof whether he is right or not. Personally I prefer testing out technical hypothesis people claim that are not documented so l came up with four simple stored procedures below to show that “Begin End” in a stored procedure does not make any difference. In SQL server, whenever a transaction is started, the global variable @@TRANCOUNT is increased by 1 so l relied on this metadata to show that “Begin End” in a stored procedure does not automatically start a transaction.
USE 2smart4school
GO
CREATE PROCEDURE dbo.AutotransactionMaker1
AS
BEGIN
SELECT @@TRANCOUNT AS TransactionCount
END
GO
CREATE PROCEDURE dbo.AutotransactionMaker2
AS
SELECT @@TRANCOUNT AS TransactionCount
GO
CREATE PROCEDURE dbo.AutotransactionMaker3
AS
BEGIN
BEGIN TRANSACTION T
SELECT @@TRANCOUNT AS TransactionCount
COMMIT TRANSACTION T
END
GO
CREATE PROCEDURE dbo.AutotransactionMaker4
AS
BEGIN TRANSACTION T
SELECT @@TRANCOUNT AS TransactionCount
COMMIT TRANSACTION T
GO
I ran the four procedures and the results showed a transaction only starts when l use the Begin Transaction clause. Just a heads up though, SQL Server always perform actions using its ACID property to keep the database in a stable state but if you haven’t explicitly started a transaction in a stored procedure and a piece of statement in the stored procedure completes execution before any other part of the stored procedure fails, the part that executed successfully will not be rolled back. If you intend the stored procedure executes as a unit a.k.a all or nothing, then you have to explicitly put the block of code in a transaction using
BEGIN TRANSACTION transactionName
do something
COMMIT TRANSACTION transactionName
The best approach is to use a try catch for your code so if a transaction fails, you can either do some other stuff or just rollback any activity that happened earlier. For example
BEGIN TRY
BEGIN TRANSACTION T1
do something
COMMIT TRANSACTION T1
END TRY
BEGIN CATCH
do something
ROLLBACK TRANSACTION T1
END CATCH
This approach makes your code better and robust. If you need only part of the code in your stored procedure to be performed as a unit, then put only that part in a transaction. Don’t put the whole code in a transaction block. Remember when you start a transaction, all tables referred in the transaction will be locked to prevent any modification until the transaction is complete. Other sessions or processes can however perform dirty reads on tables referred in your transaction block but you have to be careful and be aware of the data risk when performing such actions. Do you think there is any difference between a stored procedure with or without “Begin End”? Share it!
