Syntax on Changing return String by Concatenation? I successfully created the scalar function FN_TotalBilled in SQL Server, which gives me the total amount invoiced to a company using tables Invoices(InvoiceID, InvoiceTotal,VendorID,..) and Vendors(VendorName, VendorID,.....), and I can get the function to return an integer value, BUT I cannot figure out how to get the function to return a value of the type $Integer. Here is what I have:

CREATE FUNCTION Fn_TotalBilled (@VendorName VarChar (50) )
RETURNS Int 
BEGIN  RETURN (SELECT SUM(InvoiceTotal) FROM Invoices I join Vendors V
on I.VendorID=V.VendorID where VendorName = @VendorName ) ; END ;


Now, I can get a return value using:

SELECT dbo.Fn_TotalBilled('CompanyName') , 


Which spits out an integer. 

BUT I want as a return to get a string $integer. This is what I am trying,
which is not working:

 Select '$'+ dbo.Fn_TotalBilled('IBM')


I get the error message:
"Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '$' to data type int."

I then tried :

Select CAST('$' AS Varchar) + dbo.Fn_TotalBilled('IBM')


But that did not work out either. Can someone help me get a return string
$dbo.Fn_TotallBilled ?  I also tried to change the function by selecting SELECT '$' + ...

What else can I do?
It looks like you're expecting the integer to get implicitly converted to string. What actually happens is the other way around - the string gets implicitly converted to integer.
The simplest version of what you want is:
Select '$' + Cast(dbo.fn_TotalBill('IBM') As Varchar)

But:

You shouldn't use Varchar without a length, it's bad practice. In this case maybe Varchar(16) would do?

You're missing cents. Normal practice would be to use Decimal(16, 2) to store it or perhaps even smaller. Also as per the other answer some people prefer the Money data type because of additional decimal places for rounding purposes that might be useful if compounding interest was involved.

T-SQL isn't going to do proper regional formatting for you (though it turns out 2012 added a way in the other answer).

But above all this generally, and almost certainly, isn't/shouldn't be done in T-SQL. You should always pass raw numerical values to the caller and let them do regional currency conversion for you in the presentation layer. That's where it belongs.
The only exception I've seen is when I've included the figure in an internal error message, for example to give me a figure where the balance is wrong for debugging purposes and this isn't going to flow through to the caller.