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
Advertisements

2 Responses to “QUOTENAME function and dynamic T-SQL in Microsoft SQL Server 2008”

  1. Anonymous Says:

    seems like good Tip of the day.. 🙂 keep it up

  2. Madhivanan Says:

    Also read this to know how single quotes actually work in Dynamic SQLhttp://beyondrelational.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspxMadhivananhttp://beyondrelational.com/blogs/madhivanan


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

%d bloggers like this: