How can we help you today? How can we help you today?

How to get CASE statements on fresh lines with indenting?

How do I get CASE statements to start on their own line, preferably indented from the line before, rather than starting way, way off to the right when there's a significant amount of stuff before them.  Nested CASE statements are nearly unreadable when they're so far off the screen for complex statements.

A simple example:

SELECT
  CASE
@SPID = 3 THEN 'th'
@spid = 88 THEN 'bobasdlkfjalk;sdfjalks;djfla;skjfdlkasfjlks' +
      CASE
@SERVERNAME = 'bb' THEN 'a'
        ELSE NULL
        END
    ELSE NULL
    END AS [test];


SQL Prompt puts that inner CASE way over to the right of where the + is, and the WHEN's are based on the CASE.

What option am I missing?
DataJanitor
0

Comments

4 comments

Sort by
  • Tianjiao_Li
    Can you attach your format style file as well as the complete sample script?
    Tianjiao_Li
    0
  • DataJanitor
    Thank you!

    Complete script as SQLPrompt does it with these settings - see how far over the WHEN is?  on my screen, it's indented 2 in from the inner CASE. 

    What I would like is the inner CASE to be on a new line, indented 2 in from the previous WHEN, and then the inner WHEN intended two more.  

    This is a pretty simple example, but I've got real ones where the WHEN is so far over I can't even tell it's there without scrolling.

    SELECT
      CASE
        WHEN @SPID = 3 THEN 'th'
        WHEN @spid = 88 THEN 'bobasdlkfjalk;sdfjalks;djfla;skjfdlkasfjlks' + CASE
                                                                               WHEN @SERVERNAME = 'bb' THEN 'a'
                                                                               ELSE NULL
                                                                               END
        ELSE NULL
        END AS [test];


    Formatting style attached, with extension changed to .txt from .sqlpromptstylev2

    DataJanitor
    0
  • Tianjiao_Li
    Thanks for the format file! It's indeed not possible at the moment within SQL Prompt. Can you please kindly post this suggestion on user voice forum? https://redgate.uservoice.com/forums/94413-sql-prompt
    Tianjiao_Li
    0
  • DataJanitor
    Posted, thank you!
    https://redgate.uservoice.com/forums/94413-sql-prompt/suggestions/38200615-please-allow-case-statements-to-start-on-their-own

    DataJanitor
    0

Add comment

Please sign in to leave a comment.