Using Where in a query with a function

 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?

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

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

Try

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

Best wishes,
Fabian

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

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

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

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