Computation Column

Transact-SQL
1

Hello,

Completely new to Sql Server and had a question. I have two tables, table 1 and table 2. The "Projection Column" in table 2 is calculated by referencing the and multiplying the following columns: ObsWattage*OBJECTID from table 1. I was hoping to get a hand, as I keep running into a syntax error on 'Select':

Create Function dbo.calcuations(@ObjectID Float, @Projection Int)
Returns Float
As
SELECT @OBJECTID * ObsWattage
FROM dbo.ODOT2018
Where Projection =@Projection

Thanks!

2 
Create Function dbo.calcuations(@ObjectID Float, @Projection Int)
Returns Float
As
begin
return ( 
    SELECT @OBJECTID * ObsWattage AS Result
    FROM dbo.ODOT2018
    Where Projection =@Projection
)
end
3

Hey

Thanks for such a quick response. I think I have made a mistake, I don't think I defined projection. so should the new code be as follows using the same info from above:

Create Function dbo.calcuations(@ObjectID Float)
As
begin
return (
SELECT @OBJECTID * ObsWattage AS Result
FROM dbo.ODOT2018
)
end

4

Yes. Although for something that simple, it's very odd to call a func, just write in the SELECT statement or a view.

5

Using a select statement, how would I reference the other table? For example, If I wanted to define Projection by the above expression from table 1, I would have to define it as a function correct?

6

It's more that it's such simple logic that instead of writing:

Select * From X
Cross Apply dbo.Calculations(ObjectID)

You could just as well write:

Select * From X
Cross Apply (Select ObjectID * ObsWattage As Result From dbo.ODOT2018)
7

@AndyC

Using that approach, I could write in an entire mathematical formula with multiple variables?

8

Well, in theory, sure. The question is at what point encapsulating the logic in a function is better than writing it in-line. SQL has certain overheads in evaluating functions (even inline Table-Value Functions) that mean it's often better not to do so for very simple logic because you're punishing the query performance with the overhead. YMMV and it's worth testing to see what is best for you (particularly if the code will be frequently reused and is likely to change over time).

9

Eventually, the computed columns will have multiple references to multiple tables. I was just trying to get a feel for the concept with this equation, as these codes will be used repeatedly for every project. So I am more interested in the syntax as an insert into the computed column of each table.