2

Using TSQL, how can I get the value from a user-defined function by initially calling it in the SELECT clause but then also referencing the result in the WHERE clause? I've tried creating an alias for the result but it cannot resolve the name

SELECT C.dbo.myFunction(n.first, n.last) AS result, <other columns>
FROM C.dbo.Names AS n, <other tables>
WHERE result > 10 AND <other conditions>

Am I going about this correctly?

haventchecked
  • 1,916
  • 1
  • 21
  • 24
  • 1
    possible duplicate of [T-SQL Column alias on computed column - Invalid column name](http://stackoverflow.com/questions/6591183/t-sql-column-alias-on-computed-column-invalid-column-name) – MarcinJuraszek Aug 09 '13 at 16:51
  • 1
    See Logical Processing Order of the SELECT Statement on [this page](http://msdn.microsoft.com/en-us/library/ms189499(v=sql.110).aspx). `WHERE` happens *before* `SELECT` – Damien_The_Unbeliever Aug 09 '13 at 16:55
  • Thank you, @Damien_The_Unbeliever, that's a great reminder as to _why_ this is not a valid approach on my part. – haventchecked Aug 09 '13 at 16:56
  • @MarcinJuraszek, thank you, that link helped in addition to your answer. – haventchecked Aug 09 '13 at 18:09

2 Answers2

3

You can't use column aliases in WHERE clause. You can try following instead:

SELECT s.*
FROM 
    (SELECT C.dbo.myFunction(n.first, n.last) AS result, <other columns>
    FROM C.dbo.Names AS n, <other tables>) s
WHERE s.result > 10 AND <other conditions>
MarcinJuraszek
  • 124,003
  • 15
  • 196
  • 263
2

AS mentioned by others, you can't use an alias in a Where clause. However, you could use a common table expression instead:

With CTE AS 
(SELECT C.dbo.myFunction(n.first, n.last) AS result, <other columns>
FROM C.dbo.Names AS n, <other tables> )

SELECT * FROM CTE WHERE result > 10;
Stainy
  • 450
  • 2
  • 8