Skip to content
← SQL Prompt

How can we improve SQL Prompt?

SQL Prompt: Format SQL

Categories

JUMP TO ANOTHER FORUM

(thinking…)

Formatting SQL Transaction

It would be nice to have the code within a BEGIN TRAN and COMMIT be indented.

BEGIN TRAN
<ssss>INSERT....
<ssss>UPDATE....
COMMIT TRAN

6 votes

We're glad you're here

Please sign in to leave feedback
Signed in as (Sign out)
Close
Close

We’ll send you updates on this idea

Steven Isaacs shared this idea  ·   ·  Report…  ·  Admin →

We're glad you're here

Please sign in to leave feedback
Signed in as (Sign out)
Close
Close
Submitting...
An error occurred while saving the comment
  • Doug Tucker commented  ·   ·  Report

    I'm using version 10 of SQL Prompt and the option "Indent contents of statements" under Statements / Control Flow still doesn't apply to the contents of BEGIN TRAN / COMMIT TRAN blocks

  • RS commented  ·   ·  Report

    I don't see this feature in SQL Prompt 10

  • Lee Robinson commented  ·   ·  Report

    I have version 9 of SQL Prompt. Are the same improvements in version 9?

  • Daniel White commented  ·   ·  Report

    It would be nice, but I'm not sure it would be easy; there would be a lot to consider. How would you automatically format all of these code snippets in a sensible way?

    IF @@TRANCOUNT = 0 BEGIN TRANSACTION;
    -- ...
    IF @@TRANCOUNT > 0 COMMIT TRANSACTION;

    BEGIN TRANSACTION;
    -- ...
    IF @@ROWCOUNT = 1
    COMMIT TRANSACTION;
    ELSE
    ROLLBACK TRANSACTION;

    BEGIN TRANSACTION;
    BEGIN TRY;
    -- ...
    COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH;
    ROLLBACK TRANSACTION;
    END CATCH;

    IF @#TranCount = 0
    BEGIN TRANSACTION @$TranName;
    ELSE
    SAVE TRANSACTION @$TranName;
    BEGIN TRY;
    -- ...
    IF @#TranCount = 0
    COMMIT TRANSACTION @$TranName;
    END TRY
    BEGIN CATCH;
    ROLLBACK TRANSACTION @$TranName;
    END CATCH;

    An easy workaround is for the developer to just add extra BEGIN...END statements whereever needed to show the intent; it doesn't change the logic, and SQL Prompt will indent it:

    BEGIN TRAN;
    BEGIN;
    -- ...
    END;
    COMMIT TRAN;

    or

    BEGIN;
    BEGIN TRAN;
    -- ...
    COMMIT TRAN;
    END;