[dba-SQLServer] Formatting Numbers IN SQL

I am wanting to convert the following access line into SQL:

Format(12345,"#,##0")

Is there an equivalent SQL command to use in a stored procedure?  The 
result is then to be cast into a varchar to be appended to another varchar.


Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand