SQL Server BEGIN/END vs BEGIN TRANS/COMMIT/ROLLBACK

Question

BEGIN and END deal with code blocks. They are similar to the curly braces you see in many languages:

if (somethingIsTrue)
{ // like BEGIN
    // do something here
} // like END

In SQL, this is:

if somethingIsTrue
BEGIN
    -- do something here
END

BEGIN TRAN, COMMIT, and ROLLBACK begin and end transactions. They do not specify a new block of code; they only mark the transaction boundaries.

Note that you can write a BEGIN TRAN and COMMIT in separate blocks of code. For example, if you want code to be part of a transaction, but you don’t want to start a new one if the code is already in a transaction, you can do something like this:

declare @TranStarted bit = 0
if @@trancount = 0
begin
    set @TranStarted = 1
    begin tran
end

-- ... do work ...

if @TranStarted = 1
begin
    commit
    set @TranStarted = 0
end

Leave a Comment Cancel reply