Optional Code in T-SQL Scripts using SQLCMD

When you are writing T-SQL scripts to save away, for example one to create a new database, there are often optional bits of code you need to run sometimes, and not others.

In the creating a database script, there are quite a few parameters you will want as part of your script. The database name, the settings, whether or not to drop the database or not. Built into SQL Server Management Studio is a cool tool called SQLCMD mode. Scripts using this mode get a few scripting tools that are really useful. These tools allow you to do things like insert other script files, set environment variables, and one I particularly like, make sure you don’t accidentally execute a script if no code is highlighted using EXIT to start your script ().

Where it is really lacking is in the area of control of flow language. In this blog I am going to share a few techniques I have used to get around this when building scripts that need to optionally execute different bits of code.

If you are not quite sure what it is, back in 2013, Robert Sheldon wrote this piece on SQLCMD that covers the basics and honestly I have never used a lot of the functionality that is a part of SQLCMD and not a lot has changed over the years with it. But it serves a useful and important place when writing reusable scripts.

General Usage

For the most part when I am building a script, it starts out with one or more :SETVAR commands to make the script reusable. For example, in my standard database create script, it has a lot of variables like the following:

:SETVAR DatabaseName ArticleTest
:SETVAR PrimaryFileGroupSize 10GB
:SETVAR PrimaryFileGroupMaxSize 20GB
:SETVAR PrimaryFileGroupFileGrowth 2GB
:SETVAR PrimaryFileGroupDirectoryOverride "NONE"
:SETVAR ReadCommittedSnapship ON
:SETVAR AllowSnapshotIsolation OFF
:SETVAR QueryStore ON
:SETVAR AcceleratedDatabaseRecovery ON
:SETVAR RecoveryModel SIMPLE

These variables get used in the following manner:

ALTER DATABASE [$(DatabaseName)] 
       SET RECOVERY $(RecoveryModel);

From there I set all the settings and down in the code it fills these variables into the blanks in my scripts. When a task is repetitive as many of them are, this technique lets me just fill in the blanks. 

Some tasks, however similar each time is, have optional code to run. Like a memory optimized filegroup, or simpler to show, something like whether to execute a DROP DATABASEstatement. In the next few examples, I will show you three ways I often use SQLCMD to do something like skipping a command.

Put control of flow code into the :SETVAR command

This is one that I honestly just realized existed as I wrote this. It assume that it has always seemed so outlandish that I didn’t think it would work. (This provides yet another example of why writing is educational for the write too. I try crazy stuff to make sure I am right!).

For example, say you wanted to optionally run all a certain set of code. You can do the following:

:SETVAR test "IF 1=2"

$(Test)
SELECT 'Hi';
GO

Execute this code, and nothing will happen. Change it to IF 1=1, and you will see ‘Hi’ returned. So, any code you can press into the variable you can use. You can even do multiline code if you need it. You just need to have the first double quotes (“) on the line with :SETVAR.

:SETVAR test "
IF 1=2
  SELECT 'Hi';
 ELSE
  SELECT 'Bye'";

$(Test)

Executed, that will currently return ‘Bye’.

For the DROP DATABASE example, you could do:

:SETVAR DatabaseName ArticleTest

--create a boolean conditional expression
:SETVAR AllowDropDatabase "IF 1=1" 

$(AllowDropDatabase)
SELECT 'I would execute DROP DATABASE $(DatabaseName);'

This returns: 'I would execute DROP DATABASE ArticleTest;' Change the variable value to 1=2 and nothing will be returned.

Use in T-SQL Control of flow Statements

A lot of time, it is easier to just use a value in your code to handle the task of making a section work, likely because you already have some T-SQL control of flow language in your code. For example, you might have code that looks like:

IF EXISTS (SELECT * 
           FROM sys.databases 
           WHERE name = '$(DatabaseName)')

To this you might just add: AND 'Yes' = '$(AllowDropDatabase)‘

So now we might rewrite the optional database dropping code as:

:SETVAR DatabaseName ArticleTest

--anything other than Yes will fail
:SETVAR AllowDropDatabase "NO" 

IF EXISTS (SELECT * 
           FROM  sys.databases 
           WHERE name = '$(DatabaseName)'
             AND 'Yes' = '$(AllowDropDatabase)')
SELECT 'I would execute DROP DATABASE $(DatabaseName);';

Of course this won’t return anything because the database doesn’t exist.

In my real scripts, I have lots of statements like the following. I set the variable to DEFAULT or the name of a valid recovery model. :

:SETVAR RecoveryModel DEFAULT

IF '$(RecoveryModel)' COLLATE DatabaseDefault  
                     <> 'DEFAULT' COLLATE DatabaseDefault
   ALTER DATABASE [$(DatabaseName)] 
            SET RECOVERY $(RecoveryModel);

So, the user can set the value to a specific value, or just take the default.

Commenting Out Code

One of the tricks I probably use the most is optionally commenting out code. So just create a :SETVAR variable command that includes either a variable that contains “--” or two variables, one that represents “/*” and another that does “*/” to surround a lot of code.

For this example using the line level comments, it would look like this (you could use the $(AllowDropDatabase) variable over and over for more than two lines.)

:SETVAR DatabaseName ArticleTest

--Use "--" to disallow, and "" to allow
:SETVAR AllowDropDatabase "--" 

$(AllowDropDatabase)DROP DATABASE $(DatabaseName);
$(AllowDropDatabase)GO

If you want to comment entire blocks of code:

:SETVAR DatabaseName ArticleTest

--Use "/*" and "*/" to not execute code. "" for both to
:SETVAR RunCodeStart "/*"
:SETVAR RunCodeEnd "*/"

$(RunCodeStart)
DROP DATABASE $(DatabaseName);
$(RunCodeEnd)

The only thing to realize is that you can’t embed GO in the comment marks like this. With the first method, you can comment out the GO, or add it back. GO is a batch separator, and it is part of the too, but while you can safely comment out code including GO using:

/*
GO
*/

You will get an error if you add GO to the code using start and ending comment marks that indicate you are missing comment blocks.

Summary

The code samples presented are all very close to the level of a hack, but when you need variables that cross batches, this is the only way. SQLCMD variables also are nice because they simply are expanded as text into your code, making them easier to use than traditional variables for a lot of admin tasks.

Using the methods presented is kind of tricky for sure, but for those semi-rare occasions where you need to skip some code in a T-SQL file, where traditional control of flow language in T-SQL won’t work, these techniques work quite nicely.

 

The post Optional Code in T-SQL Scripts using SQLCMD appeared first on Simple Talk.

from Simple Talk https://ift.tt/K5yMDgF
via