Syntax on Changing return String by Concatenation?

Question

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?

Answer 1

Given tables:

CREATE TABLE dbo.Vendors
(
    VendorID integer NOT NULL,
    VendorName nvarchar(50) NOT NULL,

    CONSTRAINT [PK dbo.Vendors VendorID]
    PRIMARY KEY CLUSTERED (VendorID),

    CONSTRAINT [UQ dbo.Vendors VendorName]
    UNIQUE NONCLUSTERED (VendorName)
);

CREATE TABLE dbo.Invoices
(
    InvoiceID integer NOT NULL,
    InvoiceTotal money NOT NULL,
    VendorID integer NOT NULL,

    CONSTRAINT [PK dbo.Invoices InvoiceID]
        PRIMARY KEY CLUSTERED (InvoiceID),

    CONSTRAINT [FK dbo.Invoices VendorID]
        FOREIGN KEY (VendorID)
        REFERENCES dbo.Vendors (VendorID)
);

And sample data:

INSERT dbo.Vendors
    (VendorID, VendorName)
VALUES
    (1, N'Vendor #1'),
    (2, N'Vendor #2');
GO
INSERT dbo.Invoices
    (InvoiceID, InvoiceTotal, VendorID)
VALUES
    (1, $100.00, 1),
    (2, $200.50, 1),
    (3, $300.75, 1),
    (4, $123.45, 2);

Given the choice, you should generally prefer an inline table-valued function over a scalar or multi-statement function for performance reasons:

CREATE FUNCTION dbo.TotalBilledToVendor
(
    @VendorName nvarchar(50)
)
RETURNS table
WITH SCHEMABINDING
AS
RETURN
    SELECT
        Total = SUM(I.InvoiceTotal)
    FROM dbo.Invoices AS I
    JOIN dbo.Vendors AS V
        ON V.VendorID = I.VendorID
    WHERE
        V.VendorName = @VendorName;

A helpful index is:

CREATE NONCLUSTERED INDEX
    [dbo.Invoices VendorID (InvoiceTotal)]
ON dbo.Invoices (VendorID)
INCLUDE (InvoiceTotal);

Query using a single VendorName:

SELECT
    TBTV.Total
FROM dbo.TotalBilledToVendor(N'Vendor #1') AS TBTV;

For multiple vendor names, use apply:

SELECT
    V.VendorName, 
    TBTV.Total
FROM dbo.Vendors AS V
CROSS APPLY dbo.TotalBilledToVendor(V.VendorName) AS TBTV;

If you must do formatting work in SQL Server, use either method in srutzky's answer, or STR:

SELECT
    N'£' + STR(TBTV.Total, 13, 2)
FROM dbo.TotalBilledToVendor(N'Vendor #1') AS TBTV;

Note the money and smallmoney types are not particularly popular. You may find decimal works better.

Answer 2

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:

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

2. 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.

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

4. 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.

Answer 3

You don't need to convert the '$' to a VARCHAR since it is already a VARCHAR type. You need to convert the value coming out of the function to a string since it is currently an INT.

However, since this is currency, you should instead do the following two things:

1. Change the return type of the function to be MONEY (which is, in this case, better than DECIMAL, which is still better than the current INT type being used).

2. Use the CONVERT function, passing in a "style" value of 1 which formats MONEY typed values to strings, placing a comma after every 3 digits to the left of the decimal point. This, of course, is valid for "en-US" and some other locales, but not all of them.

   SELECT '$' + CONVERT(VARCHAR(30), dbo.Fn_TotalBilled(11134.6), 1);
   

   Returns:

   $11,134.60
   

   If you are wanting a locale that does not use that format, then since you can use the FORMAT function (introduced in SQL Server 2012) which works just like the .NET String.Format method (probably due to it actually calling the .NET method ;-).

   • The benefit of the FORMAT function is not just that it is locale-aware, but also includes the proper currency symbol and on the appropriate side (see example below).
   • The drawback of FORMAT is that it is not exactly super-fast so it can slow down queries returning many rows. It is something you will need to test to see how it works for you.

   Example:

   SELECT FORMAT(11134.6, N'C', N'fr-fr');
   

   Returns:

   11 134,60 €