QUOTENAME function and dynamic T-SQL in Microsoft SQL Server 2008

In one of my recent project I was working with dynamic SQL building and I had to use QUOTENAME function. QUOTENAME function is really very useful and sometime it is mandatory to use in SQL Server 2008/2005/2000.

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

Let us see first how it works and then I will let you know how much it will be useful in dynamic SQL.

select QUOTENAME('ritesh shah')

--result would be

--[ritesh shah]

GO

select QUOTENAME('ritesh shah','"')

--result would be

--"ritesh shah"

GO

After looking at above T-SQL and its results, question might pops up in your mind that it nothing just padding Square Bracket to the string by default and if you give any special character for covering your text, it is covering your text with that special character than where question comes about usefulness in dynamic SQL.

Well let me have one example script here first.

--CREATE one sample database to use

Create Database QuoteNameDemoDB

GO

USE QuoteNameDemoDB

GO

--create one sample table

CREATE TABLE SQL HUB

(

      ID INT

)

GO

--while generating above table, you will get an error.

--Msg 102, Level 15, State 1, Line 1

--Incorrect syntax near 'HUB'.

--the main reason for error is, blank space is not allowed

--in table name so I will do something like this script now.

CREATE TABLE [SQL HUB]

(

      ID INT

)

--table got created successfully

GO

--now let us try to access that table

SELECT * FROM SQL HUB

--AGAIN error in this statement as we can't use space.

--Msg 208, Level 16, State 1, Line 1

--Invalid object name 'SQL'.

--so now, you have to use either of the below option.

--1.)

SELECT * FROM "SQL HUB"

--2.)

SELECT * FROM [SQL HUB]

--Now suppose you want to create dynamic SQL Script which execute

--SELECT statement with every table of database, eg:

select 'SELECT * FROM ' + name from sys.objects where type_desc='USER_TABLE'

--when you generate dynamic SQL from above script, copy it and try to execute it.

--you will face error because still there is a space between SQL and Hub.

--you can modify above query with QUOTENAME and you will be ok.

select 'SELECT * FROM ' + QUOTENAME(name) from sys.objects where type_desc='USER_TABLE'

--use master

--DROP DATABASE QUOTENAMEDEMODB

--GO

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com