This article was prompted by discussions on the use of comments in T-SQL code to provide information about the intended functioning and purpose of the code.

“How do I comment and uncomment large blocks of code easily?”

This particular question caught my attention, because it is something I need to do on a very regular basis. I was keen to see how others tackled the issue, but none of the responses, from people within the SQL community that I have enormous respect for, suggested the technique that I developed for my purposes. I ran a few searches, and came up empty-handed. That prompted me to share this technique, in the hope others will find it useful.

Some really basic background info first:

T-SQL “Comment” syntax

T-SQL Books Online lists 2 generally accepted styles for including comments in code.

• The ‘single line’ comment, where comments start with a double hyphen (--) and end with the newline character at the end of the line.
• The ‘multi-line’ or ‘block comment’ that can span multiple lines. Multi-line comments are started using a slash and an asterisk (/*) and closed using the reverse pattern (*/.) I’ll call these patterns ‘block open’ and ‘block close’, for this article. The text between ‘block open’ and ‘block close’ is not evaluated by the server.

So?

If, like me, you spend a lot of time looking at your data through SSMS, you’ll probably have a lot of scripts stored to help you with repetitive tasks. I found that many of these tasks needed different ‘sections of code’ depending on the task, and I was using the ‘block comment’ format extensively, to manage these sections. That, and the question above, was why I first developed the technique below. What I discovered was a simple idea that extended the usefulness of the ‘block comment’ syntax, and provides some handy functionality for developers working in SSMS.

How?

When I ‘block comment’ any code using the /* …. */ syntax, I write the ‘single line’ comment in front of the ‘block close’ pattern. As noted above, any text between ‘block open’ and ‘block close’ (including that ‘single line’ comment) is not evaluated by the server, and as expected, my commented code now looks like this.

/*
MANY MANY LINES OF CODE CAN GO IN HERE
AND NONE WILL BE EVALUATED BY THE SERVER
..
.. BUT NOTE THE “SINGLE LINE” COMMENT STARTING THE NEXT LINE
--*/

What’s changed, though, is how I can now ‘uncomment’ the block above. Previously, I would have had to locate and disable both the ‘block open’ and ‘block close’, by deleting them both or commenting them out. Now, I can put a ‘single line’ comment in front of the ‘block open’ pattern, and the text between the open and close patterns becomes ’live’ code, including the ‘single line’ comment at the end. If I didn’t have the ‘single line’ comment in front of the ‘block close’ pattern, the unmatched pattern ‘*/’ would generate an error message and stop my scripts running (Incorrect syntax near ‘*’).

Now, the ‘block close’ pattern is commented out by the double dashes, as a ‘single line’ comment. The script runs without me needing to hunt down the errant closure, or put it back afterwards, so I can quickly enable or disable large chunks of code within my scripts. This alone saves me a great deal of time.

Wait, there’s more! Organise your scripts.

This makes the comment block much more useful in a surprising number of ways. First is that I only need to locate the opening of the comment to ‘uncomment’ the whole block without causing subsequent errors. Previously, I found it easier to keep small, related scripts as separate files. Now I keep the scripts together, but each section inactive within a ‘block comment’ as this example shows.

  -- Use blocks to keep similar scripts together
/* --SCRIPT 'A' - USE THIS SCRIPT for input date format mm/dd/yyyy
.. code goes in here
.. code goes in here
--*/ 
/* --SCRIPT 'B' - USE THIS SCRIPT for input date format dd/mm/yyyy
.. code goes in here
.. code goes in here
--*/

See that I have also added a ‘single line’ comment describing the segment’s purpose after the ‘block open’ pattern. This will remain commented whether the block is active or not, and is a great way to keep track of what your code is (meant to be) doing. You really don’t need to put the ‘single line’ dashes there, but I have found that it is an additional ‘failsafe’ if I change the script so a segment is no longer contained in a ‘block comment’.

Could I put code instead of a ‘single line’ comment after the ‘block open’ pattern?  No, as noted above, it will never get run, because either the whole block is inactive, or the entire first line is a ‘single line’ comment. If I do the same thing after the ‘block close’ pattern, though, I get a very different (and useful) result.

Tell yourself what’s happening.

Here's where the fun starts. Any code added to the same line after the ‘block close’ will be enabled when the block is commented, and inactive when the block is enabled.

Hmmm, how about a PRINT statement? I can use this to let me know which parts of my script are currently running and which are not. It may not be immediately obvious why this is useful in the sample code provided, but it can be very useful when things get a bit more complex.

  -- Add a print statement to keep track of which scripts are running
  --/* -- SCRIPT 'A' - USE THIS SCRIPT when input is integer.
  Print 'Script A active – integer input'
  ..
  --*/ Print 'Script A inactive'
   
  /* -- SCRIPT 'B' - USE THIS SCRIPT when input is char.
  Print 'Script B active'
  ..
  --*/ Print 'Script B inactive'

I now get comments in the ‘Messages’ tab telling me what has been switched off, and an easier job to trace and debug.

Script A active – integer input

(x row(s) affected)

Script B inactive

Keep your test data with your script.

Taking the idea a step further, I can comment and uncomment any blocks of code I don’t want to run every time I run a saved script. One way I use this is to keep my ‘sample code setups’ in the same file as the scripts I am working on – to set up, populate, and later drop test tables, which also means that if I need someone else’s help with the script, they automatically get a copy of the test data to go with it.

  --/* --Create and populate the test data
  CREATE TABLE #table (ID int, Comment varchar(200))
   
  INSERT INTO #table (ID, Comment)
        VALUES (1, 'First Comment')
              ,(2, 'change me')
  --*/ Print 'CREATE Table block inactive'
   
  -- Process in development
  SELECT * FROM #table
  WHERE Comment = 'change me' –- yes, normally a bit more complicated
   
  /* -- Clean up afterwards
  DROP TABLE #table
  --*/ PRINT 'DROP Table block inactive'

Once the table has been created and data added, I delete the first set of ‘line comment’ dashes (on the first line before the first ‘block open’) which deactivates the ‘Create Table’ block. I can now work on the ‘Process in development’ with the table and data. When I’m done, I activate the ‘Clean up afterwards’ section (which is also normally a bit more complex), reset the blocks ready for the next time it is needed, and save the script. My PRINT statements are helping me keep track of what's active.

Slice …

The ‘normally a bit more complicated’ bit can include complex condition statements, and if things go wrong it can be difficult to know where to start. If I have a known condition, I start by checking the statement returns the expected result for that condition.

  SELECT * FROM #table
  /* -- The following condition might return unexpected results
  WHERE ID in ( SELECT something
        FROM a-really-complex-condition
        WHERE we-are-not-sure-what-we-get
        )
  --*/ WHERE ID = 1 -- Known Condition that provides valid results

If I get the correct result for that, I can break the ‘we-are-not-sure-what-we-get’ condition into smaller bits and try switching on and off various combinations. For this example, I start by using an ‘always true’ condition (WHERE 1 = 1) and build on to it. That just allows me to switch in any subsequent part, knowing its syntax will start with ‘AND’ or ‘OR’

… and dice.

Did I mention these comments can be ‘nested’? I’ll admit this came as a surprise to me, because I had thought that the first ‘block close’ following a ‘block open’ would end the ‘block comment’ but when they are nested, SSMS somehow manages to resolve the nesting without difficulty. While the ‘outer’ comment is active, nested comment blocks can be switched active or inactive, using a line comment in front of the ‘block open’. Of course, when the outer comment is inactive, so are the nested blocks.

  SELECT * FROM #table
  WHERE 1 = 1 -- always true
  /* -- first condition to check
  AND condition1 = TRUE
  --*/  /* -- some more conditions
  AND condition2 = ( SELECT condition3
        FROM #anothertable
        WHERE 1 = 1
        /* -- Paying attention? This is a nested Comment Block
        AND NestedCondition = TRUE
        --*/        )
  --*/  --/* the comment here ‘activates’ this condition. It gets checked.
  AND Comment = 'buggy value'
  --*/

One downside here, I can’t put PRINT statements after each ‘block close’ because that interferes with the construction of the WHERE clause. I can put one after the last condition, at least.

All or nothing

What about those times you are writing updates, and you’d like to check your syntax without changing anything. Adding this to the WHERE clause of the update statement gives me a way to run the statement without updating any records until I am ready.

  update #table
        set Comment = 'changed'
        where Comment = change me'
        /* -- use 'line comment' to enable UPDATE to proceed
        and 1 = 1
        --*/ and 1 = 0

Known limitations

As with any programming technique, this does not work everywhere, for everything. For example, there’s the issue noted above when putting a command after a ‘block close’ interferes with the syntax of the command. There is also a ‘scope’ issue. If you need to use data outside a block, make sure it is available regardless of the block’s state. The following code will not work, not because multiple statements after the ‘block close’ won’t work, but because the block is inactive. Variable @p has not been created when that line executes.

  /*--
  CREATE TABLE #table (ID int, Comment varchar(200))
  DECLARE @p int
  --*/ PRINT 'CREATE Table block disabled';  SET @p = @p + 1; PRINT @p;

That’s interesting, what else can it do?

That’s pretty much up to you, now.

How about this? Set up 2 different code methods to perform the same task, getting an estimated Execution Plan for both to see which one should perform better, like this.

  -- set up different scripts to test execution plans
  --/* -- SCRIPT 1 – e.g. USE ITERATION.
  Code for the task goes here
  ..
  --*/ 
  --/* -- SCRIPT 2 – e.g USE CTE.
  Different code for the same task goes here
  ..
  --*/

Now deactivate one block, and run the other. Switch active block ‘off’, and the inactive one ‘on’, to see if the execution times support the plans

How about this?

  PRINT 'Hello World - this complex stress test needs to run a lot'
  /*
  GO 2000000
  --*/ GO

Even if you only use this technique to protect code blocks that make changes to your data (or your system),  keeping them inside comment blocks just may prevent them being run accidentally, or against the wrong system, this just could help prevent a data disaster.

If you regularly scroll across screens of code looking for an elusive ‘block close’ pattern, this can save you a heap of time and effort. More if you need to put them back in afterwards.

This simple technique creates such a versatile extension to using the ‘Block Comment’ syntax that I’m surprised I’ve never seen it elsewhere – I hope that you find it as useful as I do.

Cheers,

Zerko.