What are the challenges you face when working across database platforms? Take the survey
Options

BEGIN CATCH and IF AND

Hi Redgate Team

The following code

BEGIN CATCH
@TRANCOUNT > 0
AND XACT_STATE() <> 0
BEGIN
ROLLBACK TRAN;
END;
THROW;
END

will be formatted like this:

BEGIN CATCH
@TRANCOUNT > 0
AND XACT_STATE() <> 0
######BEGIN
########ROLLBACK TRAN;
######END;
####THROW;
END

But referring to the code style it should look like this:


BEGIN CATCH
@TRANCOUNT > 0 AND XACT_STATE() <> 0
######BEGIN
########ROLLBACK TRAN;
######END;
####THROW;
END

Can you please ensure the AND is either on the same line like the IF or aligned with the IF statement if it is sent to a new line.
Guess it is a bug in your formatting engine.

Thanks!

Torsten
Friend Of Redgate

Comments

  • Options
    Hi @torsten.strauss,

    Thanks for letting us know about this.

    I tried to replicate the issue and taking the Default style as the base with the following modifications:
    • Statements > Control Flow > Indent BEGIN END keywords
    • Expressions > Operators > Place on new Line (Never or If longer than wrap column)

    We get:
    BEGIN TRY
        -- Generate divide-by-zero error.  
        SELECT 1 / 0;
    END TRY
    BEGIN CATCH
        IF @TRANCOUNT > 0 AND XACT_STATE() <> 0
            BEGIN
                ROLLBACK TRAN;
            END;
        THROW;
    END CATCH;
    

    Is this the behaviour you desire?

    Kind regards,

    Frederico
    Software Engineer
    Redgate Software
  • Options
    edited August 4, 2017 7:57AM
    Hi Frederico,

    thanks for looking into this.
    Changing
    Expressions > Operators > Place on new Line (Never or If longer than wrap column)
    will for sure solve the issue but this is not what I would like to achieve.
    If I select
    Expressions > Operators > Place on new Line (Always)
    I would expect to see the AND Operator on a new line BUT aligned with the IF statement because it belongs to this command line.
    Changing the setting to "Always" will send the AND operator to the very beginning of the next line only.

    Thanks

    Torsten
  • Options
    Hi @torsten.strauss ,

    I may be completely misunderstanding, but wouldn't Expressions > Operators > Alignment > To first list item (instead of the default To statement) work in this case?

    With the above example you would get:
    BEGIN TRY
        -- Generate divide-by-zero error.  
        SELECT 1 / 0;
    END TRY
    BEGIN CATCH
        IF @TRANCOUNT > 0
           AND XACT_STATE() <> 0
            BEGIN
                ROLLBACK TRAN;
            END;
        THROW;
    END CATCH;
    

    If this isn't what you want, can you please format the above code as you would like to see it?

    Thanks,

    Frederico
    Software Engineer
    Redgate Software
  • Options
    Hi Frederico,

    thanks, this is exactly what I wanted to see!
    Sorry for confusion but sometimes I have problems to understand the impact of all these configuration settings.
    When you use your default query

    SELECT AddressID
    FROM Person.Address
    WHERE
    ModifiedDate BETWEEN DATEADD(MONTH, -6, GETDATE()) AND GETDATE()
    AND City = 'Bothell'
    OR LEFT(PostalCode, 2) = 'CB'
    OR PostalCode = @prefix + @suffix

    you even do not notice any difference for the first three values for the setting Expressions > Operators > Alignment but this will impact the statement posted above.
    Maybe you can add something to your default query to see the impact of the different configurations for clarification...

    Thanks again!

    Torsten
  • Options
    Hi @torsten.strauss ,

    It's great that it is now working.

    You are absolutely right. Some of our default queries need a review to increase their coverage of the formatting options (especially the ones modified/introduced in more recent versions of Prompt).

    In the meantime, the easiest way might be to preview your own query instead of using the default when editing your style:

    s4vp8jykcgql.png

    Kind regards,

    Frederico
    Software Engineer
    Redgate Software
Sign In or Register to comment.