SQL Prompt feature enhancement: apply Format SQL to selected text

Please add the ability to format only highlighted code when using the Format SQL option / Ctrl+K, Ctrl+Y.

Thank you,
Ryan

Best Answer

  • Alex BAlex B Posts: 1,052 Diamond 4
    Hi @RLilj33,

    Righto, so the selection needs to be complete/valid T-SQL as SQL Prompt needs to parse it to work out how to format and refactor (e.g. qualify names or add semi-colons) it.

    What I mentioned above was that at some point previous to the latest version, if there was a parsing error anywhere in the query window, it would fail to format a selection (of valid T-SQL) - this no longer appears to be the case, though it didn't seem like it was supposed to be doing this.  So in this case - if you had invalid SQL after the CREATE PROCEDURE and highlighted the procedure, it will format it correctly and not error due to the parsing error outside of the selection.

    As for the semi-colons; I've had some odd inclusions when copying from different sources into the forums here, so likely related to that.

    I hope that helps clarify intended behavior!

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?

Answers

  • Hi @RLilj33 ,

    What version of SQL Prompt are you currently using?  And what is the content of the window you are trying to format (the entire T-SQL of  the query window, but also let me know the selection you are trying)?

    If there are parsing errors in the query window then it may not work as it should parse the entire query window.  Having said that, in the latest version (9.4.6) I've found that at least in some circumstances, it will format the selection even if there are formatting errors elsewhere.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • RLilj33RLilj33 Posts: 8 New member
    Alex -

    I am using version 9.2.1.5713, and can format the individual sections as you indicate. But I see what is happening: I have to select a fully parse-able section of code in order for Format SQL to work.

    Consider the following code block - Format SQL can't handle this:
      (
        @id        dtID      = NULL,
        @date                dtDATE    = NULL,
        @num                dtNUMBER  = NULL,
        @string                dtSTRING  = NULL,
        @bit                BIT       = NULL,
        @char4                dtCKCODE  = NULL,
        @flag                dtFLAG    = NULL
      )

    I would have to select the entire procedure to do it. Format SQL can handle this:

    create procedure a
      (
        @id        dtID      = NULL,
        @date                dtDATE    = NULL,
        @num                dtNUMBER  = NULL,
        @string                dtSTRING  = NULL,
        @bit                BIT       = NULL,
        @char4                dtCKCODE  = NULL,
        @flag                dtFLAG    = NULL
      )
    as
    print '';

    That's fine on a small procedure, but can be a challenge when working with a complex procedure. Would be better if the selected code could be formatted instead.

    Thanks,
    Ryan

    P.S. Not sure why a semicolon is showing up after each parameter name on this thread when I post the question - those aren't there in the code.
  • RLilj33RLilj33 Posts: 8 New member
    Thanks Alex. It clarifies the answer, but my request remains. In a simple block of code, allow for formatting the code without checking the full syntax.
  • @RLilj33

    Sorry if you missed Alex's point on 20 Dec. The selection needs to be complete/valid T-SQL as SQL Prompt needs to parse it to work out how to format and refactor (e.g. qualify names or add semi-colons) it.
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
Sign In or Register to comment.