T-SQL: How to format number with commas, no decimals, and parentheses for negative numbers?

You say round up to the nearest. Do you mean round up or round to the nearest?
ie
should  1.1 and 1.9 always be 2?
should -1.1 and -.19 always be -1 or -2?

Nevertheless I have put 2 ways in here to show a sample.

The STUFFfunction takes a string as param 1(in this case the integer remaining as a string) then from postition 3 it strips out the characters for a lengths of 0 (just insert then) and replaces with the 4th param, in this case a comma.

/**
*  Sample table for testing
**/
DECLARE @stringNumbers TABLE (num VARCHAR(50))
INSERT	@stringNumbers
SELECT	'-11812.53000' UNION
SELECT	'11812.53000' UNION
SELECT	'My frog has a leaky pocket'

/**
*  2 methods due to unsurity for negative numbers
**/
SELECT	CASE 
			WHEN ISNUMERIC(num) = 0 THEN 'Invalid'
			WHEN CEILING(num) < 0 THEN '(-)' + STUFF(CAST(ABS(CEILING(num)) AS VARCHAR),3,0,',')
			ELSE STUFF(CAST(CEILING(num) AS VARCHAR),3,0,',')
		END AS NegativesUp,
		CASE 
			WHEN ISNUMERIC(num) = 0 THEN 'Invalid'
			WHEN CEILING(num) < 0 THEN '(-)' + STUFF(CAST(CEILING(ABS(num)) AS VARCHAR),3,0,',')
			ELSE STUFF(CAST(CEILING(num) AS VARCHAR),3,0,',')
		END AS NegativesDown	
FROM	@stringNumbers

Select allOpen in new window

I'm afraid that you cannot do what you request in SQL Server directly.  If the value presented out has thousands separators displayed, it will not be numeric - period.  Ditto for parentheses.  These should be handled by the display mechanism using your output data for presentation.  If your SQL Statement outputs data using these formats, it will be outputting character data, not numeric data.

Outputting the data formatted as specified is possible, but a bit tricky.  One point of clarification:  Rounding.  I will assume that -11812.53000 should round to -11812 and 11812.53 should round to 11813.  Given this limitation:

Declare @myvalue money

SET @MyValue = 19943.52200

SELECT SUBSTRING(CONVERT(varchar(12), ROUND(@myvalue + CASE WHEN SIGN(@myValue) < 1 THEN 0.4999 ELSE 0.0 END,0), 1), 1, LEN(CONVERT(varchar(12), ROUND(@myvalue + CASE WHEN SIGN(@myValue) < 1 THEN 0.4999 ELSE 0.0 END,0), 1))-3)

SET @MyValue = -19943.52200

SELECT SUBSTRING(CONVERT(varchar(12), ROUND(@myvalue + CASE WHEN SIGN(@myValue) < 1 THEN 0.4999 ELSE 0.0 END,0), 1), 1, LEN(CONVERT(varchar(12), ROUND(@myvalue + CASE WHEN SIGN(@myValue) < 1 THEN 0.4999 ELSE 0.0 END,0), 1))-3)

Select allOpen in new window

Opps, i forgot that notation thingy about the brackets was the way it was and went off on one.

As I say I am taking you to mean round up and have included the 2 methods for negative numbers. Just depends on when you apply the ceiling.

My other assumtions were that you needed to filter out non numeric values
"4) The field *must* be numeric (can't be a string)"
I took to mean that you had to ensure that the input field was numeric before attempting conversion.

bhess1 is correct of course in that the format you specify as output can only ever be considered to be numeric if it is a positive amount less than 1000.

/**
*  Sample table for testing
**/
DECLARE @stringNumbers TABLE (num VARCHAR(50))
INSERT	@stringNumbers
SELECT	'-11812.53000' UNION
SELECT	'11812.53000' UNION
SELECT	'My frog has a leaky pocket'

/**
*  2 methods due to unsurity for negative numbers
**/
SELECT	CASE 
			WHEN ISNUMERIC(num) = 0 THEN 'Invalid'
			WHEN CEILING(num) < 0 THEN '(' + STUFF(CAST(ABS(CEILING(num)) AS VARCHAR),3,0,',') + ')'
			ELSE STUFF(CAST(CEILING(num) AS VARCHAR),3,0,',')
		END AS NegativesUp,
		CASE 
			WHEN ISNUMERIC(num) = 0 THEN 'Invalid'
			WHEN CEILING(num) < 0 THEN '(' + STUFF(CAST(CEILING(ABS(num)) AS VARCHAR),3,0,',') + ')'
			ELSE STUFF(CAST(CEILING(num) AS VARCHAR),3,0,',')
		END AS NegativesDown	
FROM	@stringNumbers

Select allOpen in new window

This may be helpful for you.
http://www.siccolo.com/Articles/SQLScripts/how-to-create-sql-to-format-currency.html 
But you need to tune it according to your requirement. May be Bodestone has implemented in his/her query.

Thank you for your responses. I am trying them this evening and will let you know.  For the record, numbers should be rounded to the nearest dollar, with concessions for the limitations described above.