Hey all, today I am going to show a quick demo on QUOTENAME. When I first discovered this string function, it was one I wished I had learnt long ago as it helps simplify writing out dynamic SQL.
To quote books online, QUOTENAME “Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.” So basically it formats an identifier with delimiters so that in the event that an identifier does not comply with formatting.
The example below returns all the tables in AdventureWorks 2014 with the different types of delimiters that can be added using QUOTENAME.
USE AdventureWorks2014 GO DECLARE @tables TABLE ( [tableName] SYSNAME ) INSERT INTO @tables SELECT name AS tablename FROM sys.tables SELECT tableName AS [Raw], QUOTENAME(tableName) AS [default], QUOTENAME(tableName,'''') AS [singleQuoted], QUOTENAME(tableName,'&quot;&quot;') AS [doubleQuoted], QUOTENAME(tableName,'&lt;&gt;') AS [InequalitySigns], QUOTENAME(tableName,'@@') AS [Ats] FROM @tables GO
If you try this yourself you will notice that the last column returns NULL. This is because the @ symbol is not a valid delimiter, so you cannot pass any character in to wrap it around the field.
The example below takes all the tables and finds their corresponding entry in sys.sysobjects. Using QUOTENAME here means we do not have to specify the quotation marks around the table. There's nothing wrong with doing that, it's just that QUOTENAME looks a lot neater. (This is purely for the purposes of demoing the function; clearly you could inner join the two tables together…)
USE AdventureWorks2014 GO DECLARE @sql NVARCHAR (MAX), @count INT, @tablename sysname DECLARE @tables TABLE ( [tableName] sysname ) INSERT INTO @tables SELECT name AS tablename FROM sys.tables SELECT @count = COUNT (*) FROM @tables WHILE @count &gt; 0 BEGIN SELECT TOP 1 @tablename = tablename FROM @tables SELECT @sql = 'select * from sys.sysobjects where name = ' + QUOTENAME(@tablename,'''') from @tables EXEC (@sql) DELETE FROM @tables WHERE tableName = @tablename SET @count = @count - 1 END
All of the tables in AdventureWorks2014 are correctly formatted, so lets add one that isn't.
use AdventureWorks2014 GO CREATE TABLE [why would you even add spaces] (id INT, name VARCHAR(128) ) INSERT INTO [why would you even add spaces] VALUES (1, 'dont ever add spaces in table names')
The below query is going to fail because we do not have a properly formatted table name.
DECLARE @sql NVARCHAR (MAX) SELECT @SQL = 'SELECT * FROM ' + name FROM sys.tables WHERE name LIKE '% you even%' EXEC (@SQL) GO
Use the QUOTENAME function, and this will run correctly.
DECLARE @sql NVARCHAR (MAX) SELECT @SQL = 'SELECT * FROM ' + QUOTENAME (name) FROM sys.tables WHERE name LIKE '% you even%' EXEC (@SQL) GO