July 8, 2013 at 12:25 pm
This reminds me of a question-of-the-day I posted a few years ago: http://www.sqlservercentral.com/questions/T-SQL/65712/. This question was the result of some testing I was doing with comments, and one of the cases surprised me.
July 8, 2013 at 1:11 pm
Phil Parkin (7/8/2013)
This is exactly the technique I use in long 'utility' scripts and it works very well. I generally leave everything commented out until I need it, in case someone drops something on my F5 key :crazy:There is one additional thing I would like to be able to do though: select a block of text and then use a keyboard shortcut (like CTRL-K/CTRL-C) to automatically insert the /*[textblock]*/ around the block (/*[textblock]--*/ would be even better!).
I wonder if anyone has tried to do this?
Since several third party tools were mentioned, this would be easy enough to do in with a macro in AutoHotKey. http://www.autohotkey.com/
July 8, 2013 at 2:52 pm
I like to make use of the fact that SSMS will execute the code that is highlighted with lines like this:
Select * From Employee
-- Update Employee Set LastName = 'Smith'
Where LastName = 'Smeith'
Once I get the selection I want from the Select * , highlight everything after the -- from the Update to the end of the Where to actually execute the update.
July 8, 2013 at 11:50 pm
Alex Friedman (7/8/2013)
lshanahan (7/8/2013)
Neat trick.There is one additional thing I would like to be able to do though: select a block of text and then use a keyboard shortcut (like CTRL-K/CTRL-C) to automatically insert the /*[textblock]*/ around the block (/*[textblock]--*/ would be even better!).
I wonder if anyone has tried to do this?
All the built-in ways I could find on the various menus and such in SSMS (including stuff not shown by default) seem to use the double-dash type comment. I have heard about third-party add-ins that add macro capability to SSMS that might prove useful.
The SSMSBoost add-in does it (along with some other neat stuff).
I have SSMSBoost (very useful) but had not, until today, tried to create custom macros.
I spent some time looking through all of the available commands and could not find the one for 'surround selection with /**/' - would you be so good as to take a look and let me know what it is?
It is not possible to add an argument to any of the commands (so, for example, if you wanted a macro to insert the text 'hello world'), I can't see how you would do it) means that this command needs to pre-exist somewhere in the long long list, I think.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 8, 2013 at 11:52 pm
SQL Muscle (7/8/2013)
Phil Parkin (7/8/2013)
This is exactly the technique I use in long 'utility' scripts and it works very well. I generally leave everything commented out until I need it, in case someone drops something on my F5 key :crazy:There is one additional thing I would like to be able to do though: select a block of text and then use a keyboard shortcut (like CTRL-K/CTRL-C) to automatically insert the /*[textblock]*/ around the block (/*[textblock]--*/ would be even better!).
I wonder if anyone has tried to do this?
Since several third party tools were mentioned, this would be easy enough to do in with a macro in AutoHotKey. http://www.autohotkey.com/
Ah yes, I've used that tool before - several years ago - it was very helpful. I'll give it a shot, thanks for the idea.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 9, 2013 at 1:30 am
Phil Parkin (7/8/2013)
I have SSMSBoost (very useful) but had not, until today, tried to create custom macros.
I spent some time looking through all of the available commands and could not find the one for 'surround selection with /**/' - would you be so good as to take a look and let me know what it is?
It is not possible to add an argument to any of the commands (so, for example, if you wanted a macro to insert the text 'hello world'), I can't see how you would do it) means that this command needs to pre-exist somewhere in the long long list, I think.
You're right, there's no custom macros, but there's a built-in "/*Selection*/" button.
It appears as "SelectionToggleComment" in the list.
July 9, 2013 at 3:28 am
Alex Friedman (7/9/2013)
Phil Parkin (7/8/2013)
I have SSMSBoost (very useful) but had not, until today, tried to create custom macros.
I spent some time looking through all of the available commands and could not find the one for 'surround selection with /**/' - would you be so good as to take a look and let me know what it is?
It is not possible to add an argument to any of the commands (so, for example, if you wanted a macro to insert the text 'hello world'), I can't see how you would do it) means that this command needs to pre-exist somewhere in the long long list, I think.
You're right, there's no custom macros, but there's a built-in "/*Selection*/" button.
It appears as "SelectionToggleComment" in the list.
Now I am starting to feel really dumb.
What do you mean by 'built-in button' exactly? Are you still within SSMSBoost, or just plain SSMS?
If I look though the list of available macro commands in SSMSBoost, they are of the form area.function (eg edit.SelectAll). I could not find 'SelectionToggleComment' - is that the entire command, or does it have a prefix?
Thanks very much.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 9, 2013 at 5:14 am
Phil Parkin (7/9/2013)
What do you mean by 'built-in button' exactly? Are you still within SSMSBoost, or just plain SSMS?
SSMSBoost comes with a toolbar, and one of the buttons on it is "/*Selection*/".
If I look though the list of available macro commands in SSMSBoost, they are of the form area.function (eg edit.SelectAll). I could not find 'SelectionToggleComment' - is that the entire command, or does it have a prefix?
It's "SSMSBoost.Connect.SelectionToggleComment".
July 9, 2013 at 5:32 am
Alex Friedman (7/9/2013)
Phil Parkin (7/9/2013)
What do you mean by 'built-in button' exactly? Are you still within SSMSBoost, or just plain SSMS?
SSMSBoost comes with a toolbar, and one of the buttons on it is "/*Selection*/".
If I look though the list of available macro commands in SSMSBoost, they are of the form area.function (eg edit.SelectAll). I could not find 'SelectionToggleComment' - is that the entire command, or does it have a prefix?
It's "SSMSBoost.Connect.SelectionToggleComment".
Aha! That feature was new in SSMSBoost 2.9 and I had 2.8 installed - one upgrade later and it all works - many thanks for your time Alex.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 9, 2013 at 5:41 am
Phil Parkin (7/8/2013) select a block of text and then use a keyboard shortcut (like CTRL-K/CTRL-C) to automatically insert the /*[textblock]*/ around the block (/*[textblock]--*/ would be even better!).
I created a surround snippet to do just that - save the below, tweak how you want and import it. You then select the text, go surround, choice "CommentBlocks" from which ever folder you saved it under and tada!:-D
<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<_locDefinition xmlns="urn:locstudio">
<_locDefault _loc="locNone" />
<_locTag _loc="locData">Title</_locTag>
<_locTag _loc="locData">Description</_locTag>
<_locTag _loc="locData">Author</_locTag>
</_locDefinition>
<CodeSnippet Format="1.0.0">
<Header>
<Title>CommentBlocks</Title>
<Description>Creates Block open and Block Closed on selected code</Description>
<Author>ddfxraven</Author>
<SnippetTypes>
<SnippetType>SurroundsWith</SnippetType>
</SnippetTypes>
</Header>
<Snippet>
<Code Language="SQL"><![CDATA[
/* -- Script <name> open block
$selected$ $end$
--*/
]]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>
July 21, 2013 at 10:00 pm
Hi all, and thanks for the positive feedback here. I've not been ignoring these comments, but Murphy's Law meant this article hit the web just as I started some long-awaited touristing. As with qotd, the comments are as valuable as the question, and add a lot to the store of shared knowledge. I figured I wouldn't be the only person who's been using this (or similar) technique, but pleasantly surprised to see the different variations emerging ... and hopefully a few people who will be saved the hassles when as Phil Parkin puts it, 'someone drops something on my f5 key'.
To Mike Dougherty, yes most of the time I use this, it is for development and ad-hocs, and it does get a bit messy. Because of this, I am now a lot more likely to put explanatory comments into my code than I used to be. Usually, though, by the time I hand the code over to others, the switching has been removed - except if I want to include test data.
To 'the sqlist' and 'Terry 34156', thanks, I've learnt something from your examples as well.
Timwell, I'd guess there are many people that don't know about the 'only the highlighted/selected code is executed' trick in SSMS, either. Very handy for situations like your 'search/update' example. Good to see also, your example has the 'update' statement protected by the line comment for those 'f5 - oops' moments. Using the 'Block Comment' syntax, I'd put it like this ...
/*
Update Employee Set LastName = 'Smith'
--*/ Select * From Employee
Where LastName = 'Smeith'
For j.greene, your code uses a similar, but slightly more awkward process. I'd alter it a little bit and put the parameter definitions first, then it would look like this for testing ...
--/*
DECLARE @param1 INT
DECLARE @param2 VARCHAR(20)
--*/ CREATE PROC Test2 @param1 INT, @param2 VARCHAR(20) AS
SELECT @param1, @param2
and this when active ...
/*
DECLARE @param1 INT
DECLARE @param2 VARCHAR(20)
--*/ CREATE PROC Test2 @param1 INT, @param2 VARCHAR(20) AS
SELECT @param1, @param2
This will be a little easier to alter if needed, too.
Thanks also for the contributors discussing various tools to automate this style of comment. Didn't know any of this was available, or possible, and I will be exploring further.
And to Jesse McLain, thanks for 2 easy points and a link to some valuable discussion. Your question was based exactly on the 'nesting' capability of this technique I noted in the article (or vice versa, since yours was posted first) but it somehow didn't make the leap from a curiosity to a handy development technique.
The most exciting phrase to hear in science, the one that heralds new discoveries, is not "Eureka!" (I found it!) but "that's funny..." ... quote attributed to Isaac Asimov.
cheers,
Zerko
August 4, 2013 at 11:11 pm
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
August 5, 2013 at 11:29 am
zerko (8/4/2013)
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.
please tell me your "real" code contains a "begin transaction" before that update so when you accidentally update every employee lastname to 'Smith' you have an easy oops-fix.
August 5, 2013 at 1:00 pm
Mike Dougherty-384281 (8/5/2013)
zerko (8/4/2013)
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.
please tell me your "real" code contains a "begin transaction" before that update so when you accidentally update every employee lastname to 'Smith' you have an easy oops-fix.
Seriously?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 5, 2013 at 5:20 pm
Mike Dougherty-384281 (8/5/2013)
zerko (8/4/2013)
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.
please tell me your "real" code contains a "begin transaction" before that update so when you accidentally update every employee lastname to 'Smith' you have an easy oops-fix.
I appreciate and understand your concern here. If your shop, or your personal ethic says "You will use transactions", then use transactions, but ....
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.
Here's one I prepared earlier 😉
--Select * From Employee /*
Update Employee
Set LastName = 'Smith'
--*/
Where LastName = 'Smeith'
Remove the double dashes, and the 'update' code is safely locked back inside the block comment.
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.
cheers,
Zerko
Viewing 15 posts - 16 through 30 (of 54 total)
You must be logged in to reply to this topic. Login to reply