using aggregate function once instead of multiple times inside a stored procedure

Question

I have a stored procedure that is using a function inside of it and the function is expecting two parameters. My problem is related to performance issue see below

 CASE 
 WHEN (DATEDIFF(MINUTE,dbo.FunctionName(DatetimeField, DatetimeID), dbo.FunctionName(DatetimeField, DatetimeID))/60.0) > 8 THEN
      (DATEDIFF(MINUTE,dbo.FunctionName(DatetimeField, DatetimeID), dbo.FunctionName(DatetimeField, DatetimeID))/60.0)
 Else 0
 END 
 Else
 0
 END)
 Else
(DATEDIFF(MINUTE,dbo.FunctionName(DatetimeField, DatetimeID), dbo.FunctionName(DatetimeField, DatetimeID))/60.0)-T.lunch
END
     as 'Total'

Now what I want to do is create a temporary table so I can use it to call the function instead of the way I am calling the function each time it gets to those lines with hundred of thousands of records to go through. Any help is appreciated.

Answer 1

You can reuse expressions inside a query like this:

select (case when NewValue > 8 then NewValue else 0 end) xyz
from T
cross apply (
 select NewValue = dbo.FunctionName(DatetimeField, DatetimeID)
) x

A cross-apply used in this particular way can be used to introduce new columns and reuse their value. This technique is good for reusing common expressions in the same query.

Normal batch-level variables can be declared like this:

DECLARE @NewValue int = dbo.FunctionName(DatetimeField, DatetimeID);