4

look at this TSQL code:

SELECT A, B, C, (MAX(A) - MAX(B))*C AS 'myCalculatedColumn',
dbo.[udf_GetBetaInv]((MAX(A) - MAX(B))*C, A, B, C) as 'BetaInv'
FROM dbo.MyTable
WHERE...

is there any way to have the block: (MAX(A) - MAX(B))*C written only once and used in the myCalculatedColumn definition and also as parameter of the udf in the same statement.

I care mostly about performances, not sure if SQL Server calculates this once or multiple times but even if only once, would be better to have the SQL code more readable and compact and have only one place to modify in case we should change a formula.

Edit. I was suggested to use CROSS APPLY recently for exactly same issue: simplify SQL statement by using CTE

Community
  • 1
  • 1
Davide Piras
  • 43,984
  • 10
  • 98
  • 147

1 Answers1

5

You can define it in a CTE

WITH CTE AS
(
SELECT A,
       B,
       C,
       ( MAX(A) - MAX(B) ) * C AS myCalculatedColumn
FROM   dbo.MyTable  
WHERE...
)
SELECT *,
       dbo.[udf_GetBetaInv](myCalculatedColumn, A, B, C) as 'BetaInv'
from CTE

Or a less verbose method is to define it in a CROSS APPLY. The CTE one works in more cases.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845