QUOTENAME gotcha

 

Well, it’s not really a gotcha but rather by design. It all started with the square brackets I like to wrap around variable values in messages.

For example

declare @amount int = 54232, @code sysname = ' BHP ', @price decimal(9, 2) = 43.21

-- No brackets
print concat(@amount, ' shares of ', @code, ' traded at ', @price) 

-- Manual square brackets
print concat('[', @amount, '] shares of [', @code, '] traded at [', @price, ']') 

-- QUOTENAME
print concat(quotename(@amount), ' shares of ', quotename(@code), ' traded at ', quotename(@price))

-- Printout
54232 shares of BHP traded at 43.21
[54232] shares of [ BHP ] traded at [43.21]
[54232] shares of [ BHP ] traded at [43.21]

No brackets

It is pretty hard to tell what contributed to the extra white spaces without the brackets. From very early on in my career, I have always placed brackets around variables to help ascertain what exactly is inside the variables.

Manual square brackets

The manual placement of the extra brackets was the norm before I discovered QUOTENAME.

QUOTENAME

Coincidentally, quotename achieves the same thing by wrapping brackets around the input parameter by default. As you can see from above, the quotename version requires less effort in placing quotes, brackets and commas in a message, so as any lazy dev would do, I started to rely on quotename to wrap around variables.

QUOTENAME gotcha

This all worked fine until one day I realised quotename only works for inputs that are 128 characters or less and will return NULL if the string exceeds the limit. Doh!

Back to the manual brackets approach I guess.