SQL Server QUOTENAME function

Hi Friends,

Here is an interesting function, SQL Server QUOTENAME function: QUOTENAME() which returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier. (from Books Online)

For example:

The default delimiter is square brackets, unless you specify any of the other two delimiters (single quote ‘ or double quote “)

So where and how can this function be used?

Dynamic SQL is a good example. Suppose you have object names that contain spaces and you want to extract these object names from system catalogs to be used in your dynamic code – you will have to enclose them in delimiters.

For example;

Let us create a table in AdventureWorks that will contain spaces in the object name. After creating a sample table, we will insert a record into it.

use AdventureWorks
GO
 
create table [a funny table with spaces]
(id int)
 
insert into [a funny table with spaces]
values (1)

Now, if you query the sys.objects, you will get the object name (observe that it is without the delimiters)

Now; you want extract this object name and want to use in your dynamic code but it won’t work if you do not use the QUOTENAME function. Let us first try without the QUOTENAME function:

declare @tablename nvarchar(200) ='';

select @tablename = 'select * from ' + name + ';'
FROM sys.objects where name like '%funny%'
 
EXECUTE (@tablename);

And you get the following error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'table'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Now try the same code with the QUOTENAME function:

declare @tablename nvarchar(200) ='';
 
select @tablename = 'select * from ' + QUOTENAME(name) + ';'
FROM sys.objects where name like '%funny%'
 
EXECUTE (@tablename);

And this works 🙂

Interesting function; comments and usage welcome !