QUOTENAME()?It is a built-in function in SQL Server that adds delimiters (usually square brackets or double quotes) around a string to make it a valid SQL identifier.
Syntax:
QUOTENAME('character_string' [, 'quote_character'])
Parameters:
-
character_string– The string to be delimited. Maximum length is 128 characters. -
quote_character(optional) – The character to use for quoting. Valid options are: -
[or](default) -
"(double quote) -
'(single quote) -
(or) -
{or} -
<or>
Why Use QUOTENAME()?
SQL identifiers (like table names or column names) can include spaces, reserved words, or special characters. Wrapping them in brackets ensures they are treated correctly by SQL Server.
For example:
-
A table named
order detailswould cause an error unless written as[order details]. -
A column named
select(which is a reserved keyword) must be written as[select].
QUOTENAME() automates this for you.
Note:
- If the string exceeds 128 characters, QUOTENAME() returns NULL.
- It escapes the closing delimiter if it appears inside the string.
Use Cases:
SELECT QUOTENAME('employee name');
-- Output: [employee name]
SELECT QUOTENAME('select');
-- Output: [select]
DECLARE @tableName NVARCHAR(128) = 'employee details';
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT * FROM ' + QUOTENAME(@tableName);
EXEC sp_executesql @sql;
Using
QUOTENAME() here ensures that even if @tableName contains spaces or reserved words, the query will execute without error.When Should You Use It?
Use QUOTENAME():
When constructing dynamic SQL with identifiers that may not follow naming rules.
To safely handle user input for object names (like dynamic table or column names).
To avoid SQL injection risks (in combination with other best practices).
