How to use QUOTENAME in Sql Server

Intro

SQL Server provides the QUOTENAME function will add delimiters to a string. This is helpful when building dynamic sql queries in sql server. In this article, we will learn how to use QUOTENAME in SQL Server.

The Syntax

The basic syntax of a QUOTENAME is as follows:

SELECT QUOTENAME(string, delimiter);

• string: the string to be wrapped. The max length is 128
• delimiter: the character to wrap around the string

The valid delimiters are:

• single quotation mark ( ‘ )
• left or right bracket ( [] )
• double quotation mark ( ” )
• left or right parenthesis ( () )
• greater than or less than sign ( >< )
• left or right brace ( {} )
• backtick ( ` ).

Getting Setup

For this, we will be using docker. This is recommended for more than just using SQL Server. To find how to install docker go here: https://docs.docker.com/engine/install/

Now create a file called docker-compose.yml and add the following.

version: "3.9"
services:
  db:
    image: "mcr.microsoft.com/mssql/server"
    ports: 
      - 1433:1433
    environment:
        SA_PASSWORD: "Your_password123"
        ACCEPT_EULA: "Y"

Open a terminal and go to the folder the file is located. Then run the following.

docker-compose up

If you are looking for another good reference, you can check here: https://docs.docker.com/samples/aspnet-mssql-compose/.

Basic Example

We start by quoting a few strings on their own with different delimiters. These examples are super helpful on their own, but in the next section will run some dynamic sql examples.

SELECT quotename('employees') as tablename;

SELECT quotename('employees', '<') as tablename;

Quotename with Dynamic Sql

We can now try quotename with dynamic sql. First, create a table for us to use.

CREATE TABLE employees (
    first_name VARCHAR (50) NOT NULL,
    last_name VARCHAR (50) NOT NULL,
);

insert into employees (first_name, last_name) 
	values 
	('Keith', 'Holliday'),
	('Jon', 'Doe'),
	('Jane', 'Doe');

We now declare a variable to hold our table name.

DECLARE @tablename VARCHAR(128) = 'employees';

Then, declare a variable to hold our query.

DECLARE	@query NVARCHAR(100) = 'SELECT * FROM ' + QUOTENAME(@tablename);

Finally, execute the query.

EXECUTE (@query);  

The full sql is the following.

DECLARE @tablename VARCHAR(128) = 'employees';
DECLARE	@query NVARCHAR(100) = 'SELECT * FROM ' + QUOTENAME(@tablename);
EXECUTE (@query);