We all know that we should liberally document our code with comments, so that others (and even yourself) will know what the code is doing down the road. I like to start all of my code off with my classic remark block:
|
1 2 3 4 5 6 7 |
/****************************************************************************** $SELECTIONSTART$Description of code$SELECTIONEND$ ******************************************************************************* MODIFICATION LOG ******************************************************************************* $DATE(yyyy-MM-dd)$ WGS Initial creation. ******************************************************************************/ |
Note that this is a SQL Prompt snippet, not a snippet from within SSMS. The SQL Prompt snippet allows the use of tokens to format / do certain things. For instance, the $DATE(yyyy-MM-dd)$ token fills in the current date, and the $SELECTIONSTART$ / $SELECTIONEND$ token will select that area of text once the snippet is applied. This makes it as easy (for me) as possible to write comments about the code.
Another use for comments is to selectively run certain portions of code.
There are two kinds of comments in TSQL – individual line comments (–) and a block comment (/* */) which can span multiple lines. Generally, I prefer to use the block comment – if those comment characters are the only thing on a line, it makes it easier to select the text between them to run.
For the single-line comment, everything on the same line after the two dashes (–) is treated as a comment. For the multi-line comment, after encountering the leading /*, everything through the ending */ is treated as a comment. Interestingly, if the single-line comment immediately precedes the starting multi-line comment block marker, then that marker is treated as a comment and not as a multi-line comment block. Which allows for some interesting use of the various remark styles to control things even better.
Consider the following code:
When the beginning multi-line comment block marker is immediately preceded by a single-line comments, that block marker becomes a comment. However, the ending block marker is now not valid, so it needs to be remarked out. This allows the code to be run.
To remark out this block of code so that it can be easily switched between being commented out and being run, all you need to do is to remove the single-line comment at the beginning of the multi-line comment:
Add the single-line comment to the beginning, and you can run that code. Take it off, and the code is remarked out.
This post is for day twenty-six of my month-long blog series “A Month of SSMS Tips”. I have a landing page for the series at bit.ly/MonthOfSSMS. Please visit this page for an easy place to quickly view all of the other tips in this series.
This tip is really handy but it took me a minute to get my head around it.
Now I use it all the time to debug by using
–/*DEBUG
SELECT SOME_STUFF FROM SOME_TABLE
–*/
Then you can do a change all on –/*DEBUG to /*DEBUG and vice versa to act as a toggle for all your debug displays.
Reply
That’s a neat idea Craig! When I have debug code, I usually have a @debug parameter or variable that I can use to do that. But this is also a good use of it.
Thanks for sharing.
Reply
I like to use this approach with cte’s. You can comment out large blocks of code and test each cte section independently.
; with a as (
select * from sys.databases
)
–select * from a /*
, b as (
select * from a where database_id < 10
)
–select * from b/*
, c as (
select * from b where name like '%db%'
)
select * from c
–*/–*/
Reply