SQL Server – Changing Return String by Concatenation

functionssql serversql-server-2012

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?

Best Answer

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.

Related Question