This week’s blog is a just about a few simple SQLCMD mode tricks for SSMS, but these tricks have saved me a lot of heartache over the years. If you have not used SQLCMD mode, you probably should be. I don’t intend to introduce SQLCMD too deeply, because there are tons of articles out there that cover it quite nicely, like fellow simple-talker, Robert Sheldon does here: https://www.red-gate.com/simple-talk/sql/sql-tools/sql-server-sqlcmd-basics/.

For this blog’s purpose, all you really need to know is that it gives you some extra processing capabilities to your T-SQL editor. It is  enabled by going to the Query Menu and choosing SQLCMD mode. Once you get completely hooked on SQLCMD mode, you can have new queries open up in SQLCMD mode by default, by going to Tools; Options, then going to Query Execution and choosing a setting.

Making Sure You Don’t Accidentally Execute The Entire File By Mistake

The first use I will cover will help you stopping from running an entire file’s worth of SQL statements without meaning to. I use this mostly when doing demo code, but it certainly finds its way into some of my other code as well. The hotkeys in SSMS for hiding the results (Ctrl-R), and executing a query (Ctrl-E) are next to each other. In a demo, where you are showing code, statement by statement, accidentally executing the entire file of queries can cause you to spend unwanted time recovering with many eyes staring back at you. This is only slightly better than accidentally running code in your office system, and losing your job.

In SQLCMD, there is a command called EXIT. So if you execute the following in SSMS with SQLCMD mode turned on:

SELECT 'Hi';
:EXIT
SELECT 'There';

Nothing will occur. The :EXIT in the batch will keep the batch from executing. The file will, however, honor the batch separator (GO), so if you change your code to:

SELECT 'Hi';
GO
:EXIT
SELECT 'There';

This actually returns the results from the first query, but not the last.

----
Hi

The first batch, does execute, but the rest of the file does not. Hence, I add the following to each of my files so if I have something like the following:

SELECT 'In SQLCMD Mode, this will keep you from running the entire script'
GO
:EXIT
GO
DROP TABLE IF EXISTS Demo.DemoTable;
--100s of other statements
SELECT *
FROM   Demo.DemoTable;

If you run the entire file by accident, you will see only the following:

-----------------------------------------------------------------
In SQLCMD Mode, this will keep you from running the entire script

The other place where I use this semi-often is when working on a set of queries and I want to stop in the middle, but have a bunch of batch separators in the file.  

Quitting the Statements of a File Based on a Condition

The second thing in SQLCMD I often use is the :ON ERROR EXIT.  This is particularly great when I want to make sure that my code executes only on one server, or one type of server. I uses this generally in the opposite case from before, in that it is scripts that I expect to run the entire thing. Often when doing a release. So I might say, I want this code to only execute on my local machine (not actually named MyServerName), thusly:

:ON ERROR EXIT
IF @@SERVERNAME <> 'MyServerName'
   THROW 50000,'Not the right server',1;
GO
SELECT 'Hi';

This will return the row greeting you with a friendly ‘Hi’.  But change that to ‘NotMyServerName’, and you will see:

Msg 50000, Level 16, State 1, Line 47
Not the right server
** An error was encountered during execution of batch. Exiting.

Of course, you could use a substring on the server name if you have some standard server naming pattern, like developer machines have ‘DEVWKSTN’ in the first 8 characters, and then the script would run on any dev machine, but not any DEVSRVR, or whatever your particular, well considered naming standard you follow. Or it could be a control table on the server, or extended properties, whatever.

The other really valuable part of the ON ERROR EXIT command is that you can use it to stop execution of a file of commands if an error occurs. Usually, T-SQL will keep executing batches on most failures that don’t cause a disconnection. So:

SELECT 'hi 1';
SELECT 'hi' 2;
GO
SELECT 'hi 3';
GO

Will present you with:

Msg 102, Level 15, State 1, Line 64
Incorrect syntax near '2'.

----
hi 3

It may be particularly important that you don’t keep going in a process on an error, so you can use:

:ON ERROR EXIT
SELECT 'hi 1';
SELECT 'hi' 2;
GO
SELECT 'hi 3';
GO

Now you just see the error and you exit:

Msg 102, Level 15, State 1, Line 80
Incorrect syntax near '2'.
** An error was encountered during execution of batch. Exiting.

Naturally, by doing this, you will stop execution on ANY unhandled exception in your code. So you will need to use TRY CATCH constructs to capture any error that you want to allow to keep executing. (You can’t use TRY CATCH on a syntax error like I used, but you can with most typical errors you get from T-SQL statements that aren’t connection ending.)

One last consideration, if you are in a transaction context, it will not be rolled back.  So:

BEGIN TRANSACTION

:ON ERROR EXIT
IF @@SERVERNAME <> 'MyServerName'
THROW 50000,'Not the right server',1;
GO

SELECT @@TRANCOUNT

Will just throw the error and exit. But if you go back and execute the SELECT @@TRANCOUNT, you will see 1 (or more depending on how many times you executed it.)

Summary

With these two SQLCMD commands, you can take a bit more control of how you work with your code across multiple batches, by letting the SQLCMD tooling stop execution when either you don’t want the entire batch to execute at all, or if you want to conditionally stop execution.

Edit: Missing comma in the title, so it really read funny