A simple trick for “Block Comment” syntax in SSMS

Question

Post reply

A simple trick for “Block Comment” syntax in SSMS

Viewing 15 posts - 31 through 45 (of 54 total)

You must be logged in to reply to this topic. Login to reply

Mike Dougherty-384281 SSCrazy Points: 2794 More actions · Answer 1

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. 🙂

zerko SSCommitted Points: 1524 More actions · Answer 2

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

Primo Dang SSCrazy Points: 2643 More actions · Answer 3

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'

j-squared SSC-Addicted Points: 407 More actions · Answer 4

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.

tune SSC Enthusiast Points: 139 More actions · Answer 5

tune

SSC Enthusiast

Points: 139

July 10, 2015 at 1:01 am

#1811493

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

tune SSC Enthusiast Points: 139 More actions · Answer 6

tune

SSC Enthusiast

Points: 139

July 10, 2015 at 1:22 am

#1811498

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

trboyden SSC Veteran Points: 263 More actions · Answer 7

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.

h.tobisch SSCommitted Points: 1671 More actions · Answer 8

h.tobisch

SSCommitted

Points: 1671

July 10, 2015 at 5:45 am

#1811571

--------------

Phil Parkin SSC Guru Points: 245813 More actions · Answer 9

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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

artilugio SSC-Addicted Points: 446 More actions · Answer 10

artilugio

SSC-Addicted

Points: 446

July 10, 2015 at 6:35 am

#1811589

thanks for posting this!

trboyden SSC Veteran Points: 263 More actions · Answer 11

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.

g.britton SSChampion Points: 13862 More actions · Answer 12

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

-- */

SELECT @a, @b-2

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

dhsweg SSC Veteran Points: 248 More actions · Answer 13

dhsweg

SSC Veteran

Points: 248

July 10, 2015 at 8:03 am

#1811618

Nice tips! Thanks for posting!

fwd079 Newbie Points: 3 More actions · Answer 14

fwd079

Newbie

Points: 3

July 10, 2015 at 8:08 am

#1811622

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. :/

Phil Parkin SSC Guru Points: 245813 More actions · Answer 15

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?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.