SQL: Using Where in a query with a function

Question

SELECT col1, col2, dbo.myFunc(@param1, @param2, col1, col2) FROM theTable

how do I add a "WHERE" here to the result of the function dbo.myFunc, like WHERE resultOfMyFunc > 10 for example?

Answer 1

Just use the exact function call you use in the select in the query - the query optimizer should cache the value and use it in both places without re-evaluating the function (this is true for MSSQL, at least).

E.g.,

SELECT col1, col2, myFunc(@arg1, @arg2, col1, col2)
FROM myTable
WHERE myFunc(@arg1, @arg2, col1, col2) > 10

Answer 2

Try

SELECT 
    col1, 
    col2, 
    dbo.myFunc(@param1, @param2, col1, col2) AS result
FROM 
    theTable
WHERE
    result > 10;

Best wishes,
Fabian

Answer 3

select col1, col2, dbo.myFunc(@param1, @param2, col1, col2)
from theTable
where dbo.myFunc(@param1, @param2, col1, col2) > 10

or (not sure if this second one will work across all SQL versions)

select col1, col2, dbo.myFunc(@param1, @param2, col1, col2) as funcVal
from theTable
where funcVal > 10

Answer 4

You can just use it as you did in the SELECT:

WHERE
     dbo.myFunc(@param1, @param2, col1, col2) > 10

Answer 5

Just to add to previous answers to cover another option...

If you don't want to repeat the function call in the WHERE clause (e.g. it takes a number of parameters and makes the query less readable in your eyes), you can do:

SELECT * FROM
(
SELECT col1, col2, dbo.myFunc(@param1, @param2, col1, col2) As funcResult
FROM theTable
) x
WHERE x.funcResult > 10

Answer 6

For the love of god. HAVING select col1, col2, dbo.myFunc(@param1, @param2, col1, col2) from theTable where dbo.myFunc(@param1, @param2, col1, col2) > 10

select col1, col2, dbo.myFunc(@param1, @param2, col1, col2) as calculated
FROM theTable
HAVING calculated > 10