Dynamic SQL and the joys of QUOTENAME

SQL Server Best Practices
SQL Server Best Practices
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;

Continue reading on SQLStudies.com.

54321
(0 votes. Average 0 of 5)
Leave a reply

Your email address will not be published. Required fields are marked *