CODEWITHSUNDEEP
sqlsql-serversql-server-2014calculated-columnscomputed-field
Lim Min Yi
Lim Min Yi

Reputation: 148

Computed Column (Microsoft SQL Server 2014)

Book

| B_ID |   Name  | Unit_Price|
|------+---------+-----------|
|  B01 |   Math  |     25    |
|  B02 | Science |     34    |

Order

| O_ID | B_ID |  Quantity |Total_Price|
|------+------+-----------+-----------|
|  O01 |  B01 |     2     |     ?     |
|  O02 |  B02 |     5     |     ?     |

How can I get the Total_Price by multiplying Order.Quantity and Book.Unit_Price where Order.B_ID is a FK to Book.B_ID. Thank you!

Upvotes: 0

Views: 168

Answers (2)

William Xifaras
William Xifaras

Reputation: 5312

You can create a function where the computed column would use the function.

CREATE FUNCTION dbo.GetTotalPrice(INT @id)
RETURNS DECIMAL(19,4)
AS 
BEGIN
   DECLARE @ret DECIMAL(19,4)
   SELECT @ret = O.Quantity * B.Unit_Price
                 FROM Order O
                   INNER JOIN Book B
                   ON O.B_ID = B.B_ID
                     WHERE B.B_ID = @id
          IF (@ret IS NULL)   
             SET @ret = 0
          RETURN @ret
END

ALTER TABLE dbo.Order
   ADD Total_Price AS dbo.GetTotalPrice(O_ID)

Upvotes: 1

Lamak
Lamak

Reputation: 70678

Sounds more like you need to create a VIEW:

CREATE VIEW dbo.OrderPrice
AS

SELECT O.O_ID,
       O.B_ID,
       O.Quantity,
       O.Quantity * B.Unit_Price Total_Price
FROM Order O
INNER JOIN Book B
    ON O.B_ID = B.B_ID;

Upvotes: 2

Related Questions