CODEWITHSUNDEEP
sqlsql-server
Paul Michaels
Paul Michaels

Reputation: 16705

Using the result of a function in a SQL query

I have a SQL (Server) query like the following:

INSERT INTO MyTable (MyId, CalcAmount1, CalcAmount2) 
SELECT a.Id, a.Value1 - CalcValue, myFunc(a.Value1, a.Value2) AS CalcValue
FROM MyTable2 a
WHERE a.SomeValue = 35

So, the question is, can I do something akin to what I am attempting with the result of MyFunc() above; that is, can I alias the result and use it in the query? If not, is there another way to achieve the same thing? Obviously, I don't want to call the function twice.

Upvotes: 0

Views: 63

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You can use a subquery or CTE. SQL Server also lets you do this with a lateral join:

INSERT INTO MyTable (MyId, CalcAmount1, CalcAmount2) 
    SELECT a.Id, a.Value1 - v.CalcValue, v.CalcValue
    FROM MyTable2 a OUTER APPLY
         (VALUES (myFunc(a.Value1, a.Value2) )
         ) v(CalcValue)
    WHERE a.SomeValue = 35;

Upvotes: 1

Related Questions