CREATE FUNCTION formatting issue with Transact-SQL (SQL Server 2022)

[ISILOG-ALAM]

When formatting functions with Transact-SQL (SQL Server 2022), line breaks appear misplaced.

Input data

Which SQL and options did you provide as input?

CREATE OR ALTER
FUNCTION ConcatenateStringsWithDash (@String1 VARCHAR(255), @String2 VARCHAR(255)) 
RETURNS VARCHAR(1000) AS
BEGIN
DECLARE
@Result VARCHAR(511)
SET
@Result = @String1 + ' - ' + @String2
RETURN @Result
END

Options used :

• Tab width : 2
• Use Tabs : YES
• Language : Transact-SQL
• Keyword case : Upper
• Data type case : Upper
• Function case : Upper
• Identifier case : Preserve
• Indentation style : Standard
• AND/OR newlines : before
• Expression width : 50
• Lines between queries : 1
• Semicolon on separate line : NO

We would expect :

• RETURNS to be on a seperate line,
• everything in the BEGIN block to be indented with a tab,
• SET and the assignment to be on the same line,
• RETURN to be on its own line,
• and END to appear on a new line to clearly mark the end of the BEGIN block.

Expected Output

CREATE OR ALTER
FUNCTION ConcatenateStringsWithDash (@String1 VARCHAR(255), @String2 VARCHAR(255))
RETURNS VARCHAR(1000) AS
BEGIN
    DECLARE @Result VARCHAR(511)
    SET @Result = @String1 + ' - ' + @String2
    RETURN @Result
END

Actual Output

CREATE
OR ALTER
FUNCTION ConcatenateStringsWithDash (@String1 VARCHAR(255), @String2 VARCHAR(255)) RETURNS VARCHAR(1000) AS BEGIN DECLARE @Result VARCHAR(511)
SET
    @Result = @String1 + ' - ' + @String2 RETURN @Result END

Usage

• How are you calling / using the library?
  We are using the formatter with webpack and it is installed via libman

• What SQL language(s) does this apply to?
  Transact-SQL

• Which SQL Formatter version are you using?
  15.4.10, but reproduced on the last version (15.6.1 on this date) with the test page.

[nene]

Thanks for reporting, but unfortunately the support for procedural SQL in SQL-Formatter is pretty much non-existant, and it's unlikely to be supported in any foreseeable future because it's not really feasible to do with the current architecture.

That being said, the Transact-SQL support is actually also pretty crappy in general. There are definitely things that can be improved here.

If you're interested you can take a look inside transactsql.formatter.ts and compare it with e.g. postgresql.formatter.ts. Several things can likely be adapted from the latter. And really this whole list in there after line 65 is really completely wrong. Somebody contributed this thing a long-long time ago, and I didn't realize that this section is just copy-pasted list of chapter headings from docs.