Concatenate '$' with numeric value

Question

The SelectCommand for my GridView is as follows:

SELECT
  pubName AS Publication,
  COUNT(*) AS Total,
  SUM(CAST (price as INT)) AS Price 
FROM [SecureOrders]
WHERE DateTime >= DATEADD(day, -1, GETDATE())
GROUP BY pubName

Where I'm doing the SUM AS Price bit, I want to add a dollar sign ($) to the start, so my data displays as $109, instead of just 109. I tried just doing '$' + SUM, but of course that didn't work. Is there a way around this?

Answer 1

Like in many languages, the plus sign is an overloaded operator in T-SQL - addition or string concatenation. When any of the types involved in the operation are numeric, precedence goes to addition. Of course you can't add strings and numbers in a meaningful way, so you get an error about conversion. In order to concatenate the values as strings, you must tell SQL Server that they're all strings. One way to do this is using CONVERT:

..., '$' + CONVERT(VARCHAR(12), SUM(CAST(price AS INT))) AS Price FROM ...

Note that in Denali you will be able to avoid the converts by using the new CONCAT function, which treats all types as strings and even treats NULL values as empty strings.