Format SQL Error

SuperzSuperz Posts: 3 New member
edited July 19, 2018 4:20PM in SQL Prompt
I don't know if this is intended or something else, but when I have a script and I just want to select one of the many statements to format it I get an error pertaining to code outside of the selected code. I don't want to validate the whole script at that point. I just want to format the code within the selected code. Is there any way around this or could this possibly be a bug?

SQL Prompt version 9.2.1.5713
SSMS v17.7

Answers

  • Hi @Superz

    Can you disable formatting for blocks of code: https://documentation.red-gate.com/sp9/formatting-your-code

    If problem persists, can you share a sample script?

    Thanks.
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
  • SuperzSuperz Posts: 3 New member
    I already submitted a comment in response and I got a message saying the message needed to be reviewed after I edited it. I don't know where it went but this site is broken.
  • SuperzSuperz Posts: 3 New member

    First off it isn't that I don't know how to use SQL format. We actually use it all the time for formatting code to standardize it before it gets committing to our code repository. But maybe there is something in the settings that I'm not seeing. 

    My problem is that I when I try to format just a small piece of a code that is among lot of other code, which might not be error free, I get an error when formatting the small piece of code that I want to format. For example, when writing code for SSIS packages I write the code in SSMS first and then copy and paste it into Visual Studio (VS). Before I copy the code into VS I like to format it. In this case there can be lots of other code in this script file because this one script file can be used many times over for research, testing, and for scripts for other pieces of my SSIS package. It’s a scratch pad or playground essentially and some of the other code can be broken. I don’t want to go through a whole file and comment out or fix those other pieces of code just to get one chunk of code formatted.

    In my example below the code that I want to format is just below the first comment and before the second comment. If I highlight that and do the SQL format I get an error because of the error below the second comment.

     

     

    <p></p><p>--Code to format is below before next comment. I only</p><p>--want to format the declare and first select in this script.</p><p><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">DECLARE</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"> @Temp </span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">TABLE</span><br></p><p>(</p><p><span>&nbsp;&nbsp;&nbsp; id </span>INT,</p><p><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">&nbsp;&nbsp;&nbsp; FirstName </span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">VARCHAR</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">(</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">100</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">)</span></p><p>);</p><p><br></p><p>SELECT id,<span>&nbsp;FirstName </span>FROM @Temp;</p><p><br></p><p>--The following statement below is broken and causes the above to not </p><p><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">--format </span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">when just the above is selected for formatting regardless of the</span></p><p>--rest of the script.</p>
    <p>SELECT id,<span>&nbsp; FirstName</span></p><p>FRO @Temp;</p><p>&nbsp;</p>

     


  • way0utwestway0utwest Posts: 313 Rose Gold 1
    edited August 17, 2018 2:24PM
    This works for me. Note that your post has the "DECLARE @Temp TABLE" on the commented line. I assume that's not the case in SSMS.

    Highlight the code, then format.

    Prompt 9.1.10



Sign In or Register to comment.