Using function return value in WHERE clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using function return value in WHERE clause

Author

Topic

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-09-07 : 17:51:20

I have this query:


SELECT vs.Age, vs.DisplayName, vs.Birthday, dbo.fnGetSelectedFY('22517569', 2007) AS FiscalYear
FROM ViewStudents vs
WHERE FiscalYear > vs.Birthday
ORDER BY vs.Age

Is it possible to use the value returned from the function call in the Where clause?? The function returns a smalldatetime value, and Birthday is also a smalldatetime value.

--Nick

cvraghu
Posting Yak Master

187 Posts

Posted - 2006-09-07 : 18:18:15

Here is the processing order of select statement from BOL -

Processing Order of WHERE, GROUP BY, and HAVING Clauses

The following steps show the processing order for a SELECT statement with a WHERE clause, a GROUP BY clause, and a HAVING clause:

1. The FROM clause returns an initial result set.

2. The WHERE clause excludes rows not meeting its search condition.

3. The GROUP BY clause collects the selected rows into one group for each unique value in the GROUP BY clause.

4. Aggregate functions specified in the select list calculate summary values for each group.

5. The HAVING clause additionally excludes rows not meeting its search condition.

When "Where" clause is processed "Select" list would not have been processed. So the derived column would not be identified.

Anyway you can repeat the same function call in the "where" clause.

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-09-07 : 18:22:48

Ahhhh excellent, thanks. Worked like a charm.
--Nick

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-09-07 : 18:28:00

exec the function once and store the result in a parameter. Then reference the parameter in the select and the where clause.

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources