Dynamic SQL and the joys of QUOTENAME

5

May 11, 2017 by Kenneth Fisher

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.

5 thoughts on “Dynamic SQL and the joys of QUOTENAME

  1. bad Oedipus says:

    One thing that I have run across using the Quotename function, particularly when generating dynamic code based upon variables, is that it will return a NULL value if the length of the string you pass it exceeds 128 characters.

    SET QUOTED_IDENTIFIER OFF
    DECLARE @a VARCHAR(200), @z VARCHAR(200)
    SET @a = REPLICATE(‘a’,128)
    SET @z = REPLICATE(‘z’,129)
    SELECT
    @a AS NonQuotedStringOfAs,
    QUOTENAME(@a,”‘”) AS QuotedStringOfAs,
    @z AS NonQuotedStringOfZs,
    QUOTENAME(@z,”‘”) AS NullValue

  2. This seems something I would keep off dev knowledge as I feel they would abuse it and lazy code.

    • I don’t think you can use quotename and be lazy can you? a) it only goes up to 128 characters (as stated in an earlier comment) and b) if they try using it on a string with code it would potentially break the code. Really main use case for it is names in dynamic SQL. And it excels there 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,682 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: