Old classic movies utter this famous phrase “Gentlemen, this is off the record”. In movies this is used when talking to the press and letting them know a certain comment or two will be said, however it is not meant for publication in the media. Sometimes, we want to use words or phrases within a query window that we want SQL Server to ignore when executing the code. Fortunately, SQL Server allows us to write words or phrases that are “off the record”, with a coding technique called a commenting. Let us understand why we code comments.
All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the “Beginning SQL 2012 Joes 2 Pros tutorial series Volume “.
In the file SQLQueries2012Vol1Chapter11.0Setup.sql, if you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs”
Why we use Code Comments
Thus the main purpose of commenting techniques is to allow us to write words or phrases that are often descriptive notes or warnings of what the code is doing, when to run it and why it is necessary. This is a big benefit when other people, such as our team members, supervisors, or software testers are reviewing the code for their own purposes. In other words, comments are commonly used as a mini informational ‘readme’ file allowing other people reading it to learn more about the code being used.
Single-Line Comments for Communicating
In the example below we see a query with a single-line comment written as the first line, describing the purpose of the code below it. The comment in this example shows other developers and testers what action the code is expected to accomplish. Indeed, when we run this code block, the result set contains all the fields from the Employee table and the Location table based on the matching LocationID field using the INNER JOIN keyword.
Use Comments to Say “Why”
It is important to use comments wisely, by considering who, what, when, where, and why for describing the purpose of the code. The challenge is to write notes that balance being descriptive and brief, so anyone else that may read it days, weeks, months or even years from now can understand why it exists and what it is meant to achieve.
Single-Line Comments for Trouble-Shooting
Another great use of commenting techniques is to troubleshoot or test smaller segments of code without needing to modify or delete surrounding blocks of code that are around the segment we want to run by itself.
We suspect there is a problem with the join to the Location table and believe that the code for the Employee table segment is correct. The ability to temporarily prevent a segment of the code block from being able to execute, while the remaining code runs is a valuable troubleshooting tool. We want to test our theory by only running the segment of code with the SELECT statement for the Employee table, without having to rewrite it as a separate query, or deleting any of the code in the INNER JOIN segment.
SELECT * FROM Employee AS em --INNER JOIN Location as lo --ON em.LocationID = lo.LocationID
When we want to comment out many consecutive lines of code, we have two different commenting techniques to accomplish this goal. Use single-line comments for each and every line to be prevented from running like the last example. The second option is to use multi-line comments by placing a ‘/*’ (forward slash, asterisk with no spaces) sign at the start of the segment to be commented out and then close the comment with a ‘*/’ (asterisk, forward slash with no spaces).
SELECT * FROM Employee AS em /*INNER JOIN Location as lo ON em.LocationID = lo.LocationID*/
What if we wanted to disable a specific part of code located on the same line as other code that we wanted to remain unaffected? The goal might be to disable one word in a long line of words within the code. We can achieve this goal by using an in-line commenting technique. In-line comments are best accomplished by using the same delimiters we used for making multi-line comments. We simply mark the beginning and ending of the word, or words, that we need to comment out of the code when the query is run. In this example, we want to display a report without the GrantID field. If we try to accomplish this by placing the ‘–‘ (double hyphens) delimiter, in front of the GrantID field, it’s going to prevent all the fields in the SELECT statement from being seen by SQL Server.
SELECT --GrantID, GrantName, EmpID, Amount FROM [Grant] WHERE EmpID IS NULL -- OR Amount < 10000 OR EmpID = 7
|Msg 156, Level 15, State 1, Line 2|
Incorrect syntax near the keyword ‘FROM’
The best way to accomplish the task of preventing the GrantID field from being displayed in our result set is to use the ‘/*’ ‘*/’ delimiters. Using this technique, we can easily prevent the smallest elements of a code block from executing, without moving or affecting the code surrounding it. The following example demonstrates how this is achieved.
SELECT /*GrantID,*/ GrantName, EmpID, Amount FROM [Grant] WHERE EmpID IS NULL -- OR Amount < 10000 OR EmpID = 7
|1||92 Purr_Scents %% team||7||4750.00|
Reference: Pinal Dave (http://blog.SQLAuthority.com)