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?