CODEWITHSUNDEEP
sqlstored-procedures
Sushmita Poudel
Sushmita Poudel

Reputation: 45

How to compute value for one column from existing two columns in a table in t-sql using stored procedure?

In the below mentioned table I want to compute TotalCost as

TotalCost = Quantity * UnitPrice

How can I create a stored procedure to do so? Is it possible or not?

CREATE Table SalesTransaction
(
    SalesOrderNO int PRIMARY KEY NOT NULL,
    ProductId int FOREIGN KEY REFERENCES Product(ProductId),
    Quantity int,
    UnitPrice decimal,
    TotalCost decimal,
    DateOfSale date,
)

Upvotes: 0

Views: 74

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270411

You wouldn't. Your syntax looks like SQL Server. You can just use a computed column:

alter table SalesTransaction add TotalCost as (Quantity * UnitPrice);

Voila! The column is now part of the table definition. And it is always up-to-date.

This assumes that quantity is some sort of number. But then again, your formula already assumes that, so you should fix the data model.

Upvotes: 1

Related Questions