How can we improve SQL Prompt?

Disable SQL Prompt on certain blocks of code

Add a functionality of disabling SQL Prompt auto-formatting feature for blocks of code. Use SQL comments for 100% sql compatibility. Example:

SELECT 1, 2, 3
--<SQLPROPMT disable>
select 4, 5, 6
--</SQLPROPMT>
select 7,8,9

in the code above, the first and third statements would be auto-formatted but not the second one.

It would be especially useful for very long scripts where a single formatting template is not an option.

128 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    Piotr LipskiPiotr Lipski shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    Anonymous shared a merged idea: to have a begin tag and an end tag in between which SQL Prompt leaves the code untouched  ·   · 
    Mark AndersonMark Anderson shared a merged idea: Add NOFORMAT Comment  ·   · 

    9 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • jgonneringjgonnering commented  ·   ·  Flag as inappropriate

        I think this was added recently. paste the code below in SSMS and then format SQL (CTRL K + CTRL Y), it should only format the first and last blocks

        SELECT TOP 1 * FROM sys.[objects]

        -- SQL Prompt formatting off
        SELECT TOP 1 * FROM sys.[objects]
        -- SQL Prompt formatting on

        SELECT TOP 1 * FROM sys.[objects]

      • jreynoldsjreynolds commented  ·   ·  Flag as inappropriate

        I just ran into yet another block of code that could benefit from this.
        Also really great when you have multiple developers which work on a SQL statement, and someone has carefully formatted a block you agree on, and you don't want to mess it up.

      • Richard WellsRichard Wells commented  ·   ·  Flag as inappropriate

        We commonly have a block like this at the top of a file:

        IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[vwMyView]') AND OBJECTPROPERTY(id, N'IsView') = 1)
        DROP VIEW [dbo].[vwMyView]
        GO

        But our normal code is more spread out vertically. Format SQL blows this up. I have to select the CREATE VIEW separately and format that. (Workable, but slow.)

        A comment-embedded <SQLPrompt:NoFormat> pragma would be perfect here.

      • Tom PesterTom Pester commented  ·   ·  Flag as inappropriate

        Prompt does and excellent job in 90% of the times. In 10 % it makes some statements too verbose layout wise. Consistency is generally a good thing but now always.
        This is one of the last basic features that I miss. It already has a lot of excellent advanced features.
        I hope it makes the next beta. I voted 3 points.

      • mike hillsmike hills commented  ·   ·  Flag as inappropriate

        cant wait for this:-)
        I have complex insert scripts and have to use sql designer to open and close them to reduce space prior to printing

      • JDS44JDS44 commented  ·   ·  Flag as inappropriate

        Any feedback on plans for this request? I know there are alot of other possible formating requests out there but this would atleast give a workaround to those shortcomings if the formating could be frozen around some areas.

      • JDS44JDS44 commented  ·   ·  Flag as inappropriate

        should it use the multi-line comment instead? /*<SQLPROMT disable>*/

      • david rawheiserdavid rawheiser commented  ·   ·  Flag as inappropriate

        I would like to vote this up if possible - Sometimes you have bulk inserts that shouldn't be wrapped or complex SQL that is easier to understand if formatted in a logical and not syntactical manner. The Java format processor uses a tag in a comment @formatter:off and then @formatter:on.

      • DaverDaver commented  ·   ·  Flag as inappropriate

        Good idea. I'd like to also suggest that we would disable Code Suggestions for an entire single file. I want it on for the other pages as I research or refactor the page that contains huge amounts of dynamic SQL. I want the code suggestions to work on the other pages.

      Feedback and Knowledge Base