Skip to content
← SQL Prompt

How can we improve SQL Prompt?

SQL Prompt

Categories

JUMP TO ANOTHER FORUM

(thinking…)

Please allow CASE statements to start on their own line, preferably indented from the line before.

Please allow 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 or nested statements.

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];

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

DataJanitor shared this idea  ·   ·  Report…  ·  Admin →
How important is this to you?

We're glad you're here

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

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
  • DataJanitor commented  ·   ·  Report

    Here's a more interesting example - imagine what it'll look like on a table with a couple hundred columns, or worse, with the maximum of 1,024 columns.

    USE [tempdb]

    DROP TABLE IF EXISTS [test]

    CREATE TABLE [TEST]
    (
    [a_Watch_How_Each_CASE_is_farther_to_the_right] TINYINT NOT NULL PRIMARY KEY
    ,[b] TINYINT NOT NULL
    ,[c] TINYINT NULL
    ,[d] TINYINT
    ,[e] TINYINT
    )

    INSERT INTO [TEST]
    VALUES
    (
    1, 2, 3, 4, 5
    )
    ,(
    2, 3, 4, NULL, 5
    )

    DROP TABLE IF EXISTS [test2]

    CREATE TABLE [TEST2]
    (
    [a_Watch_How_Each_CASE_is_farther_to_the_right] TINYINT NOT NULL PRIMARY KEY
    ,[b] TINYINT NOT NULL
    ,[c] TINYINT NULL
    ,[d] TINYINT
    ,[e] TINYINT
    )

    INSERT INTO [TEST2]
    VALUES
    (
    1, 2, 3, 4, 5
    )
    ,(
    2, 4, 4, 3, 5
    )

    SELECT
    CASE
    WHEN [X].[a_Watch_How_Each_CASE_is_farther_to_the_right] IS NULL THEN 'Y only'
    WHEN [Y].[a_Watch_How_Each_CASE_is_farther_to_the_right] IS NULL THEN 'X only'
    ELSE CASE
    WHEN ([X].[a_Watch_How_Each_CASE_is_farther_to_the_right] <> [Y].[a_Watch_How_Each_CASE_is_farther_to_the_right]) THEN 'a,'
    ELSE ''
    END + CASE
    WHEN ([X].[b] <> [Y].[b]) THEN 'b,'
    ELSE ''
    END + CASE
    WHEN
    (
    ([X].[c] <> [Y].[c])
    OR
    (
    (
    ([X].[c] IS NULL)
    AND ([Y].[c] IS NOT NULL)
    )
    OR
    (
    ([X].[c] IS NOT NULL)
    AND ([Y].[c] IS NULL)
    )
    )
    ) THEN 'c,'
    ELSE ''
    END + CASE
    WHEN
    (
    ([X].[d] <> [Y].[d])
    OR
    (
    (
    ([X].[d] IS NULL)
    AND ([Y].[d] IS NOT NULL)
    )
    OR
    (
    ([X].[d] IS NOT NULL)
    AND ([Y].[d] IS NULL)
    )
    )
    ) THEN 'd,'
    ELSE ''
    END + CASE
    WHEN
    (
    ([X].[e] <> [Y].[e])
    OR
    (
    (
    ([X].[e] IS NULL)
    AND ([Y].[e] IS NOT NULL)
    )
    OR
    (
    ([X].[e] IS NOT NULL)
    AND ([Y].[e] IS NULL)
    )
    )
    ) THEN 'e,'
    ELSE ''
    END
    END AS [Differences]
    ,[X].[a_Watch_How_Each_CASE_is_farther_to_the_right] AS [X_a]
    ,[Y].[a_Watch_How_Each_CASE_is_farther_to_the_right] AS [Y_a]
    ,[X].[b] AS [X_b]
    ,[Y].[b] AS [Y_b]
    ,[X].[c] AS [X_c]
    ,[Y].[c] AS [Y_c]
    ,[X].[d] AS [X_d]
    ,[Y].[d] AS [Y_d]
    ,[X].[e] AS [X_e]
    ,[Y].[e] AS [Y_e]
    FROM [TEST] AS [X]
    FULL OUTER JOIN [TEST2] AS [Y]
    ON (([X].[a_Watch_How_Each_CASE_is_farther_to_the_right] = [Y].[a_Watch_How_Each_CASE_is_farther_to_the_right]))