Words: 537

Time to read: ~ 2.5 minutes

Selective Execution:

Recently I came across a question in Stack Overflow (SO) that said the following:

The other day I was trying to hit another button on the menu but hit Execute – which executed the whole code and ended up deleting some tables. I have always found this scary that hitting one button can execute the whole code.

I want SQL Server to execute code only when something is selected. Is it possible? Or can SQL Server prompt before executing a query?

Can We Restrict It?

I thought this is a great question because it can be answered in 4 different ways…

1. It can be answered simply:

Just be careful with what you select and run!

2. You can get 3rd party tools to help you catch this

Red Gate, for example, even warn you three times!

That green “squiggle” is the new code analyser. If we move to the left, a little light-bulb will show and warn us, again, that this is bad.

 

Even if we ignore this, and just try to execute the query, they try a 3rd and final time to stop you!

3. You can comment out the query.

This will enable you to hit F5 and nothing will get executed. If you want to selectively run the code, all you need to do is highlight it.

And, because I have Red Gate tools, I get the lovely warning about having a DELETE with no WHERE clause again.

The problem with this is that we lose everything else available to us: Code Analysis, Parsing Information, anything! We could have accidentally typed DELETED FROM dbo.AllInLine and we’d be none the wiser until we tried to run it.

There has to be another way…

4. Is there another way?

PowerShell has the same difficulties as SSMS in this regard; this regard being the people using it anyway. You hit F8 in PowerShell, it runs the command that’s highlighted/the command of the line it’s on but if you hit F5, it runs everything!

There’s this cool method (or at least I thought it was cool) of placing the command return at the start of a PowerShell script to stop mistaken F5 clicks.

Can we do the same with T-SQL and SSMS? Yes, we can! Enter the command NOEXEC which, according to the docs,

Compiles each query but does not execute it.

This is great because it means that we have the ease of use like the comments method but still get the other features like Code Analyser and Parsing!

Now, if we want to run the code, all we need to do is remove the first SET NOEXEC ON or highlight the code we want to run!

SET NOEXEC Method

I like this way, seems much more professional. Plus it gives you more options that the others.

So that’s the answer that I gave!

That means I’ve hit my quota of SO questions this month!

Sweet! I can go back to learning now 🙂