CODEWITHSUNDEEP
sqlsql-serversql-server-2008
andree
andree

Reputation: 3234

SQL Server using procedure to calculate column value

I have a procedure that calculates the price of travel based on some values from other tables.

Procedure looks like:

create procedure CalculateTravelPrice(@ID int)
as
begin
return
(select SalaryForOneDay from Driver where driver.PersonID=@ID)+
(select SalaryForOneDay from Driver where driver.PersonID=@ID)
end

I'm trying to add column that uses this procedure to calculate the value:

alter table RegisteredTravel
add PriceForTravel as (CalculateTravelPrice(RegTravelID))

but I get error saying

Msg 195, Level 15, State 10, Line 2
'CalculateTravelPrice' is not a recognized built-in function name."

So the question is how to correctly use procedure to calculate this value? Is this even a good practice or maybe there is a better way?

Upvotes: 2

Views: 3182

Answers (1)

Jon Egerton
Jon Egerton

Reputation: 41569

You should turn your proc into a udf (user defined function). You can then use that for your calculated column. I don't think procs can be used this way.

Upvotes: 2

Related Questions