Use QUOTENAME( )… [or else!]
Lately, I seem to be reading a lot of "bracket-slapping" code:
SET @Sql = 'ALTER INDEX ['
+ @IndexName + '] ON ['
+ @DBName + '].['
+ @SchemaName + '].['
+ @ObjectName + '] REORGANIZE '
This is much easier to read (and easier to type!) than using the QUOTENAME() function:
SET @Sql = 'ALTER INDEX '
+ QUOTENAME(@IndexName) + ' ON '
+ QUOTENAME(@DBName) + '.'
+ QUOTENAME(@SchemaName) + '.'
+ QUOTENAME(@ObjectName) + ' REORGANIZE '
BOL says that QUOTENAME() "[r]eturns a Unicode string with the delimiters added to make the input string a valid Microsoft SQL Server delimited identifier." So these two code snippets are pretty much interchangable, right?
But QUOTENAME() would be pretty lame if all it did was slap brackets around a string, wouldn't it? Fortunately (or unfortunately, if you've got a lot of "bracket-slapping" code laying around) it does more: it "escapes" embedded bracket characters. For example, let's take the table name 'Sales YTD [2009]', and try to create it.
-- Demonstrate that QUOTENAME() is safer than bracket-slapping.
DECLARE @Sql NVARCHAR(4000) = ''
DECLARE @TableOne sysname = 'Sales YTD [2009]'
-- Expected to fail: table name has spaces, brackets.
SET @Sql = 'CREATE TABLE ' + @TableOne + ' (x INT)'
PRINT '(1) ' + @Sql
EXEC sp_executesql @Sql
PRINT ' '
-- Expected to fail: bracket-slapping doesn't handle brackets.
SET @Sql = 'CREATE TABLE [' + @TableOne + '] (x INT)'
PRINT '(2) ' + @Sql
EXEC sp_executesql @Sql
PRINT ' '
-- Expected to succeed.
SET @Sql = 'CREATE TABLE ' + QUOTENAME(@TableOne) + ' (x INT) '
+ CHAR(10) + ' '
+ 'DROP TABLE ' + QUOTENAME(@TableOne)
PRINT '(3) ' + @Sql
EXEC sp_executesql @Sql
PRINT ' '
The first attempt uses the name with no "decoration"; it's not a legal identifier name, and so will fail. The second attempt uses "bracket-slapping"; the brackets in the table's name will cause this to fail. The third, and correct, method uses QUOTENAME(). Running the code above results in this output:
(1) CREATE TABLE Sales YTD [2009] (x INT)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'YTD'.
(2) CREATE TABLE [Sales YTD [2009]] (x INT)
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'Sales YTD [2009] (x INT)'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Sales YTD [2009] (x INT)'.
(3) CREATE TABLE [Sales YTD [2009]]] (x INT)
DROP TABLE [Sales YTD [2009]]]
Moral of the story: always use QUOTENAME() - unless you enjoy bug hunts!
About This Post
Share this post with friends