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 basic syntax of a QUOTENAME is as follows:
SELECT QUOTENAME(string, delimiter);
The valid delimiters are:
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.
If you are looking for another good reference, you can check here: https://docs.docker.com/samples/aspnet-mssql-compose/.
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;
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.
The full sql is the following.
DECLARE @tablename VARCHAR(128) = 'employees'; DECLARE @query NVARCHAR(100) = 'SELECT * FROM ' + QUOTENAME(@tablename); EXECUTE (@query);