CODEWITHSUNDEEP
sql-serverstringconcatenationquotename
LearnByReading
LearnByReading

Reputation: 1883

When/ why would you use QUOTENAME in SQL?

I understand that the QUOTENAME function can be used to add square brackets ([], the default behaviour) or some other character wrapper to strings. It doesn't work for longer strings (over 128 characters).

So... Why/ when would you use it instead of the more conventional and far more easily readable string concatenation?

Why would you not just concatenate a single quote or a square bracket at the beginning and end of a term and use this function instead?

Upvotes: 20

Views: 24574

Answers (3)

Joel Coehoorn
Joel Coehoorn

Reputation: 416049

You use QUOTENAME() mainly when you need to build dynamic sql. Dynamic SQL is something you should avoid as much as possible, but in rare situations it can be the best way to solve a problem. When building dynamic SQL, QUOTENAME() is the correct way to ensure table and column name identifiers — which may include unwanted characters like spaces — won't cause problems with your final SQL statement.

Upvotes: 9

Robert McKee
Robert McKee

Reputation: 21477

It is/was specifically designed for quoting column/table/database names – sysnames. For example, this: SELECT QUOTENAME('abc[]def') returns [abc[]]def] while SELECT '[' + 'abc[]def' + ']' returns [abc[]def] which is invalid to use as a column/table/database name.

Additionally, the SQL-99 standard is to quote by using a single quote character, and while current versions of Sql Server continue to use brackets, it may in the future (or be configurable to be) use the SQL-99 standard. In which case, all code using QUOTENAME will continue to function correctly, while code that tries to do its own escaping will fail.

There is more subtle implications as well. Since QUOTENAME has the exact same limitations as sysname, should Microsoft ever decide to change sysname to be longer than 128 characters (256 maybe? 32767 maybe?), it would be assumed that QUOTENAME would then also be able to handle these increased sizes. Using QUOTENAME is a safe(r) way of taking a column name from a possibly untrusted source and using it as a sysname – no matter the current/future database settings without having to worry about the edge cases (like ] or ' inside the input) and whether it will allow the string to break out of the column name to create SQL injection attacks. I probably wouldn't depend solely on this feature for security, but to be used in one of many layers of protection.

Upvotes: 36

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

QuoteName is primarily designed for SYSNAME like datatypes. This sysname datatype is unicode of 128 characters which is NVARCHAR(128). Hence if it is more than 128 characters you need to use traditional way of concatenating. But we can use QUOTENAME for varchar, nvarchar and sysname datatypes..

declare @test NVARCHAR(1000) = replicate('a',500)
declare @testsysname sysname = replicate('a',500)

select QUOTENAME(@test) -- this returns null
select QUOTENAME(@testsysname) --this displays only for 128 character with brackets

Upvotes: 2

Related Questions