What are the challenges you face when working across database platforms? Take the survey
Options

Various Formatting "Anomalies" - 9.1.0.4138 - Includes a HOWLING BUG

PDinCAPDinCA Posts: 642 Silver 1
edited February 28, 2018 1:30AM in SQL Prompt
BUG FIRST
When a proc contains a large volume of XML in the form of a document with some string concatenations, SQL Prompt renders the code unreadable. It places elements of the statement out to column 3588, and another to 3626, both preceded by thousands of blanks. Obviously, this is a very bad thing! As the code is proprietary, once the Support Ticket is opened, I'll attach the file in its original and corrupted states.

Other Issues/Failures/Regression-omissions
From Uservoice: https://redgate.uservoice.com/forums/94413-sql-prompt/suggestions/8979655-don-t-indent-begin-end-bloc-after-as-in-function-p - it says that was done back in July 2015. It appears it has fallen through the cracks as every Proc I've formatted now indents the surrounding BEGIN...END.
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
GO

ALTER PROCEDURE dbo.cusp_Allocate_Calculate
    ( @iMaximumMilliSecondsBeforeProcedureMustReturn int = 10800000
    , @iFilterMasterID                               int = NULL )
AS
    BEGIN

        SET NOCOUNT ON;
...
    END;
GO
If there's an option I have yet to find, buried in some obscure place in the Formatting Styles dialog...... Do tell, please...

The Procedure signature above was gemmied into that form because if we don't put parentheses around the parameters - something in 13 YEARS of T-SQL I have NEVER, EVER, done - we get a silly-looking signature area for long-named procedures (not authored by me, but they are part of the codebase here):
ALTER PROCEDURE dbo.usp_Allocate_Maintenance_ManageBatch_AllocateStrategyForecasts @iUserID                                       int = 1       -- IOAdmin is a Good Default
                                                                                 , @xAllocateStrategyIDList                       xml = NULL    --<root><i>1</i><i>2</i></root>
                                                                                 , @iInventoryFilterMasterID                      int = NULL    -- Intersect Inventories with Distinct Allocation Strategies
                                                                                 , @bRunAll                                       bit = NULL    -- If true will execute on all Allocation Strategies
                                                                                 , @bForceBuildFlag                               bit = NULL
                                                                                 , @iMaximumMilliSecondsBeforeProcedureMustReturn int = 3600000 -- 1 Hour per 1000 timeout 
AS
The signature really needs its own treatment - it cannot fall in line with the general parenthesis formatting rules within the body of the code...

Although the rules for DML (in my style) state "Right aligned to statement", when formatting the following, INTO is left-aligned. That's a bug, it seems.
SELECT CAST(1 AS bit) AS Abit
INTO   #temp
 WHERE 0 = 1;

Prompt makes a bit of a mess of CTE formatting
SELECT CAST(1 AS bit) AS Abit
INTO   #temp
 WHERE 0 = 1;
/* -----------------------------------------------------------------------------
** Regular Comments box
** -------------------------------------------------------------------------- */
;
WITH CTE_1
  AS
      ( SELECT CAST(1 AS bit) AS Abit
         WHERE 0 = 1 )
SELECT *
  FROM CTE_1 c
 WHERE c.Abit = 1;
becomes
SELECT CAST(1 AS bit) AS Abit
INTO   #temp
 WHERE 0 = 1;
/* -----------------------------------------------------------------------------
** Regular Comments box
** -------------------------------------------------------------------------- */



;
WITH CTE_1
  AS
      ( SELECT CAST(1 AS bit) AS Abit
         WHERE 0 = 1 )
SELECT *
  FROM CTE_1 c
 WHERE c.Abit = 1;

Note the extra blank lines... It only does this when a flower-box comment preceeds the CTE-query.
If you remove the leading semicolon, look, no blank lines! Magic!

Block comments get no love. First line indented; remaining lines frozen to their original column indentation.

CTE queries lose formatting when an OUTER APPLY appears in the SELECT that consumes the CTE.
Jesus Christ: Lunatic, liar or Lord?
Decide wisely...
Sign In or Register to comment.