A simple trick for “Block Comment” syntax in SSMS
-
zerko (8/5/2013)
If I comment out the first line, the "Where" clause remains as it is. I don't update every employee, only those named "Smeith" ... that was the whole point.[snip]
I've only shared this method because I personally find it extremely useful. If it doesn't suit your needs, you don't have to use it.
I have seen incompletely-highlighted code get run. I've been advising code "safety" at lot lately. It looked like an opportunity for someone (clearly not you) who isn't as careful with what code to comment and what to highlight+F5... could have a bad day over.
This isn't about the commenting convention at all. I understand any kind of sloppy code execution can lead to a bad day.
Phil Parkin (8/5/2013)
Seriously?No, actually... it was more tragically comedic than serious. 🙂
-
Mike Dougherty-384281 (8/5/2013)[hrI have seen incompletely-highlighted code get run. I've been advising code "safety" at lot lately. It looked like an opportunity for someone (clearly not you) who isn't as careful with what code to comment and what to highlight+F5... could have a bad day over.
This isn't about the commenting convention at all. I understand any kind of sloppy code execution can lead to a bad day.
So Transactions aren't really going to help in a "highlight+F5" scenario, either.
Maybe coaching people to use some sort of simple way to determine what records will be affected by an update statement, while keeping the update code 'inactive', before they run it?
If only such a technique was available. :Whistling:
cheers,
Zerko
-
zerko (8/4/2013)
Amazing where a bit of collaboration can take an idea - by combining the 'toggle trick' example from 'the-SQList' with Timwell's 'select/update' example, discovered another way of combining the select and update statements to use the same 'where' condition.Select * From Employee /*Update Employee
Set LastName = 'Smith'
--*/
Where LastName = 'Smeith'
So I can now write quite a complex update statement if needed, it's protected in a comment block so it won't be run 'accidentally', and when my where condition selects the correct lines, all I need to do is 'double dash' comment the 'select' statement, run the update, then remove the double dashes.
Thanks again for the inspiration.
Cheers,
zerko
I changed that piece of code a bit so I can easily change the from clause in both the update and select at the same time:
Select * From e /*Update e
Set LastName = 'Smith'
--*/
from Employee e
Where LastName = 'Smeith'
-
Interesting comment tricks, thanks for sharing.
I often keep multiple utility/investigation queries in the same file, too, and handle them similarly, but I like to make blocks inactive using "if 1 = 0" because then SSMS syntax-highlights the SQL:
if 1 = 0 begin
select *
from Foo
update Foo
set Bar = 42
where Baz = 'answer'
end
..etc.
I can then either just select the SQL I want to run and press F5 (and having the syntax highlighting makes me feel more comfortable that I won't make a mistake doing so), or just change 0 to 1 to make an entire block 'active'.
Cheers.
-
Very useful thanks, I especially like the last bit for testing 2 execution plans. Thanks a lot for posting this.
Failure is an event not a person: Zig Ziglar
-
Very useful thanks, I especially like the last bit for testing 2 execution plans. Thanks a lot for posting this.
Failure is an event not a person: Zig Ziglar
-
As a point of clarity in code purpose, re-usability, script length, and the "what the heck was this guy thinking" factor, I find commented out code to be a poor coding practice.
If the goal is re-usability, the code belongs in a user defined function or stored procedure. Commented out code in a script cannot be used by anything, which makes it unusable by any coherent standard.
For general commenting practices, if you are already using SSMS to write your code, why wouldn't you just use the batch comment and un-comment toolbar buttons or shortcut keystrokes to comment and un-comment sections of code, during what I would presume to be debugging sessions? Final code output should only have known working and verified code, and documentation comments as necessary.
Anyone that has worked on a project involving multiple SQL programmers can tell you of the horrors caused by the obtuse nature of commented out code. While working on a recent data conversion project, I can't tell you how many times I have run into data validation faults that were caused because someone commented out fields in an INSERT or UPDATE statement or commented out a call to a critical validation script. Not to mention the inherent frustration of trying to read through a script containing a bunch of commented out garbage code that may or may not have served a purpose at one time or another. Most of the time someone leaves in commented code, they don't even bother leaving a comment to say why it was there. Hence the "what the heck was this guy thinking" factor.
-
--------------
-
trboyden (7/10/2015)
As a point of clarity in code purpose, re-usability, script length, and the "what the heck was this guy thinking" factor, I find commented out code to be a poor coding practice.If the goal is re-usability, the code belongs in a user defined function or stored procedure. Commented out code in a script cannot be used by anything, which makes it unusable by any coherent standard.
For general commenting practices, if you are already using SSMS to write your code, why wouldn't you just use the batch comment and un-comment toolbar buttons or shortcut keystrokes to comment and un-comment sections of code, during what I would presume to be debugging sessions? Final code output should only have known working and verified code, and documentation comments as necessary.
Anyone that has worked on a project involving multiple SQL programmers can tell you of the horrors caused by the obtuse nature of commented out code. While working on a recent data conversion project, I can't tell you how many times I have run into data validation faults that were caused because someone commented out fields in an INSERT or UPDATE statement or commented out a call to a critical validation script. Not to mention the inherent frustration of trying to read through a script containing a bunch of commented out garbage code that may or may not have served a purpose at one time or another. Most of the time someone leaves in commented code, they don't even bother leaving a comment to say why it was there. Hence the "what the heck was this guy thinking" factor.
I agree with you for production code. The general thrust of the thread, however, is around the creation of utility or setup scripts which are run and debugged manually. In these cases, being able to rapidly and safely comment and uncomment blocks of code is a justifiable aim.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
thanks for posting this!
-
Phil Parkin (7/10/2015)
I agree with you for production code. The general thrust of the thread, however, is around the creation of utility or setup scripts which are run and debugged manually. In these cases, being able to rapidly and safely comment and uncomment blocks of code is a justifiable aim.
Agreed, but the problem stems that those utility scripts, more often than not, become "production" code due to programmer laziness or shortcuts taken due to poor project planning. Thus advocating the approach is improper. Teach people to code properly in the first place, and you won't need shortcuts. Every script should be treated as if it were production code. If you do that, you create a good and automatic habit of writing good code.
-
Here's one way I use this trick:
Suppose you're working on a stored procedure. You want to be able to test each section. However, you have a problem in that the parameters are defined in the header. I solve it with sections like this:
-- Proc definition
CREATE PROC foo (@a int, @b-2 int)
...
/* -- Test this section
DECLARE @a int = 42
DECLARE @b-2 int = 3
-- */
Now, when I want to test the section, I highlight from the DECLARE statements to the end of the section to test and hit F5.
Gerald Britton, Pluralsight courses
-
Nice tips! Thanks for posting!
-
Weird article, had to read twice to know its just about a hack to comment/uncomment SQL.
I thought it was about a new SQL feature to "activate" commented code with some reserved keycode combination. :/
-
trboyden (7/10/2015)
Phil Parkin (7/10/2015)
I agree with you for production code. The general thrust of the thread, however, is around the creation of utility or setup scripts which are run and debugged manually. In these cases, being able to rapidly and safely comment and uncomment blocks of code is a justifiable aim.
Agreed, but the problem stems that those utility scripts, more often than not, become "production" code due to programmer laziness or shortcuts taken due to poor project planning. Thus advocating the approach is improper. Teach people to code properly in the first place, and you won't need shortcuts. Every script should be treated as if it were production code. If you do that, you create a good and automatic habit of writing good code.
OK, I roundly disagree with your absolute statement:
"Thus advocating the approach is improper."
Here is a part of a setup script I wrote recently. I developed it on one environment, then moved to QA, Prod etc.
--Verifications Framework
-------------------------
--Author:Phil Parkin
--Created:2015-06-03
--Script to be run on the ETL server
--*** Initial set-up only. Do not run when the live tables have been populated – the data would be deleted ***
--Config database
--__________________________________________________________________________________________________________________________________________
/*
--#region Create the SSISConnStringVariable table
use Config;
go
if object_id('cfg.SSISConnStringVariable', 'U') is not null
drop table cfg.SSISConnStringVariable;
create table cfg.SSISConnStringVariable
(
SSISConnStringVariableNameId int identity(1, 1)
not null
,SSISConnStringVariableName sysname not null
,SSISEnvironmentName sysname not null
,constraint PK_SSISConnStringVariable primary key clustered (SSISConnStringVariableNameId asc)
);
create unique index ix_SSISConnStringVariableName on cfg.SSISConnStringVariable(SSISConnStringVariableName);
--*/
--#endregion Create the SSISConnStringVariable table
--__________________________________________________________________________________________________________________________________________
On my system, with SSMSBoost (which supports collapsible regions) installed, I see this as follows:
So, when I am running the script in QA, for example, all I have to do is comment out the first /* to make this block 'active'.
Why is this such bad practice?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 15 posts - 31 through 45 (of 54 total)
You must be logged in to reply to this topic. Login to reply