I’m a big fan of dynamic SQL. In the past I’ve written a How to, a Best Practices and even a Generic Dynamic SP although that last one was a bit so so in my opinion. Depending on what type of dynamic code you are writing QUOTENAME will be your best friend.

Let’s start with what it does.

Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.

It’s probably easier to understand with a quick demonstration:

-- QUOTED_IDENTIFIER being off lets me use "s (double quotes)  
-- around the string to simplify things with the 's (single quotes).
-- Otherwise, you have to escape the single quotes. For future 
-- reference that would look like this '''' where the outer to 's
-- tell you it's a string and the single ' on the inside is 
-- doubled to escape it.
SET QUOTED_IDENTIFIER OFF;
DECLARE @teststring varchar(50) = "te'st]me";
SELECT QUOTENAME(@teststring), QUOTENAME(@teststring,"'");
SET QUOTED_IDENTIFIER ON;

Basically, it escapes any occurrence of the second parameter within the first parameter. So when would we be using it in dynamic SQL? Well, probably the most common way I’ve used it is when I’m building a list of commands I want to run.

-- The default value for the second parameter is ]
SELECT 'ALTER DATABASE '+QUOTENAME(name)+' SET OFFLINE;'
FROM sys.databases
WHERE state_desc = 'ONLINE'
  AND database_id > 4;

ALTER DATABASE [Test] SET OFFLINE;

ALTER DATABASE [AdventureWorksDW2014] SET OFFLINE;

ALTER DATABASE [AdventureWorks2014] SET OFFLINE;

You’ll notice that []’s were put around the database names. Had there been a ] in the database name it would have been escaped and the code would still run. It’s a good idea to do something like this anytime you reference schema names, object names, database names, index names etc. It’s not that people put []’s inside of a name very often but it does happen and you don’t want your code to break. Using QUOTENAME appropriately will make your dynamic code far more durable in the face of odd names.

Brackets (]) are the default and by far the most common usage, although I have used the single quote every now and again. In fact, I’ve used quotename just to dynamically put single quotes around a string before.

The second parameter can be any of the following characters

• left or right bracket ([])
• single quote (‘)
• double quote (“)
• left or right paren ‘()’
• left or right curly brackets ({})
• greater and less than signs (<>)

As with all dynamic SQL if you are using QUOTENAME you want to be careful that you aren’t leaving yourself open to SQL Injection.

Filed under: Dynamic SQL, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: dynamic sql, microsoft sql server, T-SQL