UDF导致的列上的where子句 [英] Where clause on a column that's a result of a UDF

问题描述

我有一个用户定义的函数(例如myUDF(a,b))，该函数返回一个整数.

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

我试图确保该函数仅被调用一次，并且其结果可以用作WHERE子句中的条件:

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尝试将x检测为列，但这实际上是计算值的别名.

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

如何重新编写此查询，以便可以对计算值进行过滤，而不必多次执行UDF?

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?

推荐答案

如果您使用的是SQL Server 2005及更高版本，则可以使用交叉应用:

If you are using SQL Server 2005 and beyond, you can use Cross Apply:

Select T.col1, T.col2, FuncResult.X
From Table As T
    Cross Apply ( Select myUdf(T.col1, T.col2) As X ) As FuncResult
Where FuncResult.X > 0

这篇关于UDF导致的列上的where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！