An underrated, and perhaps less well-known T-SQL function is QUOTENAME(). If you haven’t heard of it, chances are that it could do wonders for your dynamic SQL scripts.

To quickly recap quoting, consider the following script:

SELECT N'DROP PROCEDURE '+OBJECT_SCHEMA_NAME([object_id])+N'.'+[name]+N';'
FROM sys.procedures
WHERE [name] LIKE N'%test';

What happens if one of your object names contains a space, a quote, an apostrophe, a square bracket, etc? You’ll end up with a syntax error, or even worse, a SQL injection attack (pretty elaborate, but still quite possible). To solve for this, we quote the object names. In SQL Server, you can surround schema and object names with double quotes (if you’ve set QUOTED_IDENTIFIER) or square brackets.

Simple, right?

SELECT N'DROP PROCEDURE ['+OBJECT_SCHEMA_NAME([object_id])+N'].['+[name]+N'];'
FROM sys.procedures
WHERE [name] LIKE N'%test';

But just adding a [ before and a ] after won’t work if your evil user as embedded square brackets or a semicolon in the object name. What if your object name is “Testing [quoting]; test”?

QUOTENAME() to the rescue!

The built-in QUOTENAME() function in T-SQL takes care of all the woes of sanitizing and cleaning your strings.

SELECT QUOTENAME('Testing [quoting]; test');

(No column name)
--------------------------
[Testing [quoting]]; test]

And here’s the best part: QUOTENAME() doesn’t just work with square brackets – you can tell it how you want the string to be escaped, by adding a second argument. Do you want the string to be escaped as a valid T-SQL string, or a double-quoted object name? Not a problem:

SELECT QUOTENAME('Testing [quoting]; test', ''''),
       QUOTENAME('Testing [quoting]; test', '"');

(No column name)            (No column name)
--------------------------- -------------------------------
'Testing [quoting]; test'	"Testing [quoting]; test"

So instead of using REPLACE() to substitute all the single-apostrophes with double-apostrophes, try the QUOTENAME() function.

But there’s a catch.

I found out the hard way that QUOTENAME() uses the sysname, a.k.a. nvarchar(128), datatype. What’s more, if your inputs are longer than 128 bytes, the function won’t throw an error – it will just return NULL. If you want to help me change this, please upvote my feedback item.