BEGIN CATCH and IF AND

torsten.strauss

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

FredericoJ

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

torsten.strauss

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

FredericoJ

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

torsten.strauss

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

FredericoJ

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:



Kind regards,

Frederico