I wanted to share a technique I use when writing scripts that include ad-hoc SQL statements. I think this technique makes the script cleaner and also easier to edit.

First of all a warning. I don’t recommend using ad-hoc SQL for anything that will be executed by end-users or application code. Ad-hoc SQL queries usually have some kind of string injection in them and that leaves you vulnerable to SQL injection attacks. Having said that, as an admin, I find using them unavoidable when writing scripts for maintenance or reconnaissance.

Sample Case

Say I wanted to script out the creation of a database but I don’t want to rely on hardcoded properties like in this script:

CREATE DATABASE [TEST123]
ON PRIMARY (
	NAME = 'TEST123_PRIMARY_data', 
	FILENAME = 'F:\SQL-DATA\MSSQL$SQL1\TEST123_PRIMARY_data.mdf', 
	SIZE = 1024MB, 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 1024MB
	)
LOG ON (
	NAME = 'TEST123_log', 
	FILENAME = 'F:\SQL-LOGS\MSSQL$SQL1\TEST123_log.ldf', 
	SIZE = 1024MB, 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 1024MB
	);

What if I wanted to parameterize this script so that I could easily set these properties at runtime. I could try something like this:

DECLARE @DBName NVARCHAR(128) = 'TEST123';
DECLARE @DataDir NVARCHAR(MAX) = 'F:\SQL-DATA\MSSQL$SQL1\';
DECLARE @LogDir NVARCHAR(MAX) = 'F:\SQL-LOGS\MSSQL$SQL1\';

DECLARE @cmd NVARCHAR(MAX) = 'CREATE DATABASE [' + @DBName + ']
ON PRIMARY (
    NAME = '''+ @DBName + '_PRIMARY_data'', 
    FILENAME = ''' + @DataDir + @DBName + '_PRIMARY_data.mdf'', 
    SIZE = 1024MB, 
    MAXSIZE = UNLIMITED, 
    FILEGROWTH = 1024MB
    )
LOG ON (
    NAME = '''+ @DBName + '_log'', 
    FILENAME = ''' + @LogDir + @DBName + '_log.ldf'', 
    SIZE = 1024MB, 
    MAXSIZE = UNLIMITED, 
    FILEGROWTH = 1024MB
    );';

PRINT @cmd;

EXECUTE (@cmd);

This is fine but to me it looks a little messy due to the concatenated strings and beginning, ending, and escaped quotes. Escaped quotes can become confusing to deal with and I have always run into the problem where I forgot to escape one of the quotes and have to scrutinize the query to find where. Even though this script doesn’t include it, eventually I will have to write a script that has nested escaped quotes and that is madness to troubleshoot.

Doing It Cleanly

I have found that tokenizing the ad-hoc query makes it cleaner to read and easier to make changes later. It does make a little more coding because I will need to replace the tokens with the actual values but I think this is a good trade-off. The names I use for the tokens I borrowed from a similar convention SQL Server uses for SQLCMD variables which is $_(TokenName). This way if I need to run these in SQLCMD without it conflicting with SQLCMD variables.

DECLARE @DBName NVARCHAR(128) = 'TEST123';
DECLARE @DataDir NVARCHAR(MAX) = 'F:\SQL-DATA\MSSQL$SQL1\';
DECLARE @LogDir NVARCHAR(MAX) = 'F:\SQL-LOGS\MSSQL$SQL1\';

DECLARE @cmd NVARCHAR(MAX) = 'CREATE DATABASE [$_(DBName)]
ON PRIMARY (
    NAME = ''$_(DBName)_PRIMARY_Data'', 
    FILENAME = ''$_(DataDir)$_(DBName)_PRIMARY_Data.mdf'', 
    SIZE = 1024MB, 
    MAXSIZE = UNLIMITED, 
    FILEGROWTH = 1024MB
    )
LOG ON (
    NAME = ''$_(DBName)_Log'', 
    FILENAME = ''$_(LogDir)$_(DBName)_log.ldf'', 
    SIZE = 1024MB, 
    MAXSIZE = UNLIMITED, 
    FILEGROWTH = 1024MB
    );';

-- Replace tokens
SET @cmd = REPLACE(@cmd, '$_(DBName)', @DBName);
SET @cmd = REPLACE(@cmd, '$_(DataDir)', @DataDir);
SET @cmd = REPLACE(@cmd, '$_(LogDir)', @LogDir);

PRINT @cmd;

EXECUTE (@cmd);

I know that there are still escaped quotes in the query but without all the other noise caused by concatenating strings and beginning and ending quotes I think it’s more clear what’s wrapped inside the quotes and where the beginnings and endings are.