Skip to content
← SQL Prompt

How can we improve SQL Prompt?

SQL Prompt: Format SQL

Categories

JUMP TO ANOTHER FORUM

(thinking…)

Add options for CASE WHEN ELSE END

I'd like to be able to configure a style for CASE statements using options like the following:

  • New line after THEN
  • New line before ELSE
  • New line after ELSE
  • ELSE match indent of CASE
  • END match indent of CASE

It would also be nice to have a threshold setting (in number of characters) so the formatting settings are only applied if the length of a CASE statement exceeds the threshold.

64 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

JasonR 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
  • Ken Concilio commented  ·   ·  Report

    I don't see it in 7.5.0.787... I love the suggestions above!

  • Anonymous commented  ·   ·  Report

    Maybe 7.3 is confused with CASE as in case statements vs CASE as in UPPERCASE

  • Jordon Pilling commented  ·   ·  Report

    I would to see the CASE statement get its own version of the existing "Start new line at Boolean operator" option.

    For instance, I like having my AND's and OR's in WHERE clauses on new lines, but when it comes to the CASE statement, I much prefer to keep the WHEN conditions on one line.

    i.e. instead of:

    SELECT CASE WHEN [ORDER_CODE] = [A_CODE]
    AND [A_prodname] <> '' THEN [A_prodname]
    WHEN [ORDER_CODE] = [U_CODE]
    AND [U_prodname] <> '' THEN [U_prodname]
    WHEN [ORDER_CODE] = [P_CODE]
    AND [P_prodname] <> '' THEN [P_prodname]
    ELSE 'INVALID CODE'
    END AS [Description]
    FROM dbo.vw_whatever;

    Have:

    SELECT CASE WHEN [ORDER_CODE] = [A_CODE] AND [A_prodname] <> '' THEN [A_prodname]
    WHEN [ORDER_CODE] = [U_CODE] AND [U_prodname] <> '' THEN [U_prodname]
    WHEN [ORDER_CODE] = [P_CODE] AND [P_prodname] <> '' THEN [P_prodname]
    ELSE 'INVALID CODE'
    END AS [Description]
    FROM dbo.vw_whatever;

  • Blaž Dakskobler commented  ·   ·  Report

    My suggestion:

    CASE [...]
    \tab WHEN ... THEN ...
    \tab WHEN ... THEN ...
    \tab ELSE ...
    END AS ...

  • Piotr L commented  ·   ·  Report

    Great suggestion. Couldn't agree more. 3 votes from me.

  • Wes commented  ·   ·  Report

    To add on to AltonD's suggestions, I would like to see the AS Alias on a new line after the case statement. This will make it much easier to find the alias name with large case statements.

  • AltonD commented  ·   ·  Report

    I agree, and if the CASE could begin on a new line and a new line be possible after the END. This isolates the 'code' for the CASE from other parts of the SELECT.