CODEWITHSUNDEEP
sqlsql-server
Sebas Silva
Sebas Silva

Reputation: 115

How to specify a column name in a SQL Server function?

The problem is that I have several registers of an specific buy order because, each register is of a different product in the same purchase, first I did this function:

CREATE FUNCTION TOTALORDEN (@id_orden INT)
RETURNS float
AS
BEGIN
    DECLARE @total FLOAT

    SELECT @total = UnitPrice * Quantity - UnitPrice * Quantity * Discount 
    FROM [Order Details] 
    WHERE @id_orden = OrderID

    RETURN @total
END

but the problem with that function was that only printed the result of the last register.

Then I decided to use SQL arithmetics on the select instruction, and the function is this:

CREATE FUNCTION TOTALORDEN (@id_orden INT)
RETURNS table

    RETURN (SELECT (UnitPrice * Quantity) -
                   (UnitPrice * Quantity * Discount) 
            FROM [Order Details] 
            WHERE @id_orden = OrderID)

And the error it shows is:

Msg 4514, Level 16, State 1, Procedure TOTALORDEN, Line 3
CREATE FUNCTION produced an error because no column name was specified for column 1.

Part of the data in the table:

enter image description here

Upvotes: 0

Views: 2499

Answers (3)

Jesús López
Jesús López

Reputation: 9241

SQL Server Scalar functions perform bad in SQL Server, you should try to avoid them as much as possible. An inline table valued function is better, however in this case, I would prefer a view like the following:

CREATE VIEW OrderDetailsSummary
WITH SCHEMABINDING
AS
    SELECT od.OrderID, SUM (ISNULL(od.Quantity * od.UnitPrice * (1 - CAST(od.Discount AS money)), 0)) as OrderTotal, COUNT_BIG(*) AS OrderLineCount
    FROM 
        dbo.OrderDetails od
    GROUP BY
        od.OrderID

GO

This view can be indexed, making it lightning fast:

CREATE UNIQUE CLUSTERED INDEX UX_OrderDetailsSummary
ON OrderDetailsSummary(OrderID)

You can now get order total and order line count with a simple query like this:

SELECT *
FROM OrderDetailsSummary WITH (NOEXPAND)
WHERE OrderID = 10260

It is extremely efficient. I only requires 2 logical reads:

(1 row affected) Table 'OrderDetailsSummary'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

This is the execution plan:

enter image description here

Additionally the view can be JOINED with other views and tables. For example you can get the order information along with summary data:

SELECT O.OrderID, O.CustomerID, O.OrderDate, S.OrderTotal, S.OrderLineCount
FROM
    dbo.Orders O
    LEFT OUTER JOIN dbo.OrderDetailsSummary S WITH (NOEXPAND)
        ON O.OrderID = S.OrderID

Upvotes: 0

SQLApostle
SQLApostle

Reputation: 590

SQL does not like it if you don't tell it what the column returned is named. I have used a column name of FinalPrice , which you can change to what you want to call it.Also if you are planning to use this function to update the table etc, having the orderid returned in the table would be helpful. This should work :

CREATE FUNCTION [TOTALORDEN] ( @id_orden INT )
RETURNS TABLE
RETURN
(
SELECT OrderId, SUM(( [UnitPrice] * [Quantity] ) - ( [UnitPrice] * [Quantity] * [Discount]) ) AS FinalPrice
FROM
    [Order Details]
WHERE [OrderID] = @id_orden
Group BY OrderId
)

Also, if you just need the total order sum returned, using a table valued function is an overkill. You should use 

CREATE FUNCTION [TOTALORDEN] ( @id_orden INT )
RETURNS FLOAT
AS
BEGIN
DECLARE @total FLOAT

SELECT
    @total = SUM (([UnitPrice] * [Quantity]) - ([UnitPrice] * [Quantity] * [Discount]))
FROM
    [Order Details]
WHERE [OrderID] = @id_orden 

RETURN @total
END

Having spaces in your table names is not recommended either, you should think about refactoring that as well

Upvotes: 1

Squirrel
Squirrel

Reputation: 24803

I guessed you missed the aggregate function SUM()

DECLARE @total float

SELECT @total= SUM (UnitPrice*Quantity-UnitPrice*Quantity*Discount) 
FROM [Order Details] 
WHERE @id_orden=OrderID

RETURN @total

Upvotes: 1

Related Questions