Where clause on a column that's a result of a UDF

Question

I have a user defined function (e.g. myUDF(a,b)) that returns an integer.

I am trying to ensure this function will be called only once and its results can be used as a condition in the WHERE clause:

SELECT col1, col2, col3, 
       myUDF(col1,col2) AS X
From myTable
WHERE x>0

SQL Server tries to detect x as column, but it's really an alias for a computed value.

How can you re-write this query so that the filtering can be done on the computed value without having to execute the UDF more than once?

Answer 1

With Tbl AS 
(SELECT col1, col2, col3, myUDF(col1,col2) AS X  
        From table myTable  )

SELECT * FROM Tbl WHERE X > 0