This is a Format SQL request; not for SQL Prompt, but rather for this website. I don't know how many hundreds of examples have been posted where the author spent quite a bit of time formatting an example, only to have the posted code totally bastardized. Have at least a <code> tag that would present the content within the tag in a fixed-width font and not remove leading spaces. I've seen some posts where the poor soul has tried 2-3 times to get their poin t across only to have the presented code rendered in a way that completely loses intent.

These are the typical formatting styles I use that I would like to see definable in SQL Prompt. Currently, I use a style that gets me close then manually format the remainder to the code:

Regarding the desired format of

1) SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY all lined up
2) TAB after SELECT, followed by first 'column'
3) Subsequent columns on separate lines and lined up with the first one.
4) TAB after any CASE followed by first WHEN
5) Subsequent WHENs lined up with first one
6) TAB before THEN (i.e. indented one tab from WHEN)
7) ON/AND in JOIN, WHERE and HAVING lined up, indented one TAB and TAB following (e.g. [TAB]ON[TAB]…; [TAB]AND[TAB]…)
8) No TAB (one space only) after JOIN/ORDER BY/GROUP BY but subsequent lines are indented and lined up.

When the option to insert a values clause with default values for an INSERT statement is in force, the default values used by SQL prompt ignore nulls. e..g if your table is CREATE TABLE foo (bar int NULL) and you let SQL Prompt build an INSERT with default values, it uses 0 as the default for column bar. This is wrong. The true default is NULL. It would be better if SQL Prompt just used a value clause like :

VALUES (DEFAULT)

and let SQL Server figure out the default. Worse yet, check out this table and the generated code:

CREATE TABLE #foo (bar int NOT NULL DEFAULT 42)

INSERT INTO #foo
(
bar
)
VALUES
(0 )

SQL Prompt ignores the *defined* default value

I have different wants for parentheses rules: Collapse short content or not, Having Parentheses on separat lines or leading the content, etc. It all depends on the context. But currently the setting is global.

I would like to be able to define one rule for, say, Primary Key definitions, and another rule for, say, the columns of a table.
I would want one thing for Control Flow sections, and another thing for Variables declarations.

Today, I need to choose which one to be dominant (or stop using FORMAT SQL on the entire script, but just highlight the block where I want the formatting to occur). It would be a huge time-saver, if I could just format everything every time, without FORMAT SQL command messing up various places.

In SQL Prompt, Formatting Styles, I would like to differentiate the “Short parentheses contents” behavior, so it didn’t impact the primary key when I add a Constraint Primary Key Clustered at Create Table time.
I like my PK fields to stand out, not hide in a block of text.
But I also like any other listing of a few fields to be kept on a single line, and not bloating the screen for no reason at all.
Currently, I can’t have both.

In short, I want

CREATE TABLE dbo.Orders
(
OrderDate DATETIME NOT NULL,
OrderID INT NOT NULL,
CustomerID INT NULL,
CONSTRAINT PK_Orders
PRIMARY KEY CLUSTERED (
OrderDate ASC,
OrderID ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY];

instead of this:

CREATE TABLE dbo.Orders
(
OrderDate DATETIME NOT NULL,
OrderID INT NOT NULL,
CustomerID INT NULL,
CONSTRAINT PK_Orders
PRIMARY KEY CLUSTERED (OrderDate ASC, OrderID ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY];

If I declare variables and assign them values (either in declaration or subsequent SET/SELECT statement, it would be very helpful if SQL Prompt, along with displaying the declaration (e.g. @MyVar int (Scalar variable) in the pop-up, would display the last know value. So, if my declaration was:
DECLARE @MyVar INT = 123
the popup would show something like @MyVar int = 123 (Scalar variable).
I realize some might be rather long (string) or not displayable at all (e.g. table variables, cursors, etc.) but what appropriate showing the value that was set and last known could be very helpful in line 2,345 of the script that states AND EffDate > @MaxEffDate without having to go back to the top of the script.

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

When concatenating strings, it'd be nice to be able to align the "+" sign. The following is what I get now:
SELECT CASE
WHEN Col = 1
THEN 'Col 1'
ELSE ''
END + CASE
WHEN Col = 2
THEN 'Col 2'
ELSE ''
END + CASE
WHEN Col = 3
THEN 'Col 3'
ELSE ''
END + CASE
WHEN Col = 4
THEN 'Col 4'
ELSE ''
END AS ConcatStr
FROM #Test;

But what I'd like to get is:

SELECT CASE
WHEN Col = 1
THEN 'Col 1'
ELSE ''
END
+ CASE
WHEN Col = 2
THEN 'Col 2'
ELSE ''
END
+ CASE
WHEN Col = 3
THEN 'Col 3'
ELSE ''
END
+ CASE
WHEN Col = 4
THEN 'Col 4'
ELSE ''
END AS ConcatStr
FROM #Test;

Currently Code Analysis does not implement all of the items that Find Unused Variables and Parameters does.

Specifically I know it's missing:
- Declared variables that are used before a value is assigned
- Assigned values that are never used

Ex.
With this code:

DECLARE @Test NVARCHAR(1)
DECLARE @Test2 INT
DECLARE @Test3 DATE

SET @Test = 1

SET @Test = N'Test' + @Test2

Code Analysis will flag the DECLARE for @Test for having a NVARCHAR of size 1 and @Test3 for not being used, but it will not flag @Test2 for being used before a value is assigned to it and for the values assigned to @Test1 never being used.

It would be nice if those additional checks were in Code Analysis so that we could see the real time feedback we get with the other checks. As it stands, we can get to it, but devs have to remember to run that extra check once they are done.

Currently SQL Prompt only has one option for formatting insert statements which poses a problem for us with multi value set inserts.

Ideally we'd be able to select a formatting for a single value set insert and another format for a multi value set insert.

This would allow us to do something like this:

-- Single Value Set Insert
INSERT INTO TestTable (TestName,
~~~~TestValue,
~~~~TestDate)
VALUES (N'Something',
~~~~140,
~~~~'1/1/2019')

-- Multi Value Set Insert
INSERT INTO TestTable (TestName, TestValue, TestDate)
VALUES (N'Something', 140, '1/1/1900'),
~~~~(N'SomethingElse', 150, '1/2/1900'),
~~~~(N'SomethingElse2', 160, '1/2/2000')

The issue for us is that we have need of the single value set insert format in our stored procedures and most of our normal code as it allows for only one direction of scrolling (up/down instead of up/down and side to side).
But in some population and testing scripts we really need the multi value set insert format (or something close to it) to prevent the scripts from being ridiculously long and for ease of identifying where one value set ends and another starts.

It would be great if SQL Prompt gave us support for this.

I would like to control the list behavior on a clause by clause basis so that I can separate the formatting on my DECLARE statements differently than lists within SELECT and INSERT statements. As it stands, these are conflicting changes. So, the only way to get each variable declared on a separate line is to also have every column in other statements on a separate line. I'd like to be able to do BOTH of these:

DECLARE @GUID UNIQUEIDENTIFIER = NEWID(),
@Char CHAR(1) = 'A',
@Int INT = 1,
@Datetime DATETIME = CURRENT_TIMESTAMP,
@Decimal DECIMAL(18,10) = 1.0,
@Date DATE = CURRENT_TIMESTAMP,
@Time TIME = CURRENT_TIMESTAMP,
@Bit BIT = 1,
@Money MONEY = 1.00,
@Binary VARBINARY(18) = 0x01,

INSERT INTO Test.Test
(ID, Code, Description, Name, ExternalID, CreatedBy, CreatedUTC, LastUpdateBy, LastUpdateUTC, RowVersion,
DLLoadDate, ServerExecutionID
VALUES (@Int, @Char, @Char, @Char, @Char, @Char, @Datetime, @Char, @Datetime, @Binary, @Datetime, @Int)

I write a LOT of unit tests, usually at the rate of 15:1 to 20:1 to my executing code. I have these standardized and templated to make them easier and more consistent to build. e.g. @GUID UNIQUEIDENTIFIER = NEWID(),
@Char CHAR(1) = 'A',
@Int INT = 1,
@Datetime DATETIME = CURRENT_TIMESTAMP,
@Decimal DECIMAL(18,10) = 1.0,
@Date DATE = CURRENT_TIMESTAMP,
@Time TIME = CURRENT_TIMESTAMP,
@Bit BIT = 1,
@Money MONEY = 1.00,
@Binary VARBINARY(18) = 0x01,

These go into my VALUES clause for INSERT statements, but I current have to do all of this manually, which takes a LOT of time when you're writing several thousand of these every single week. What I'd like to do is configure SQL Prompt to use my custom set instead of default values when it generates an INSERT statement.

So instead of seeing:
VALUES (0, -- ID - int
N'' , -- Code - nvarchar(256)
SYSDATETIME(), -- CreatedUTC - datetime2(7)
NULL, -- RowVersion - varbinary(18)

I'd like to see
VALUES (@Int, -- ID - int
@Char, -- Code - nvarchar(256)
@Datetime, -- CreatedUTC - datetime2(7)
@Binary, -- RowVersion - varbinary(18)