capturing derived columns for repeated use

[letheology]

I'm creating a view which encapsulates several layers of computations. I'd like to write a query like

SELECT func1(a,b,c) AS result1, func2(d,e,f,result1) AS result2 FROM table

This query does not work, since result1 is only an alias. I could accomplish what I want by simply writing


SELECT func1(a,b,c) AS result1, func2(d,e,f,func1(a,b,c)) AS result2 FROM table

The problem with this code is that it ends up calling the function func1 twice. With the code I'm writing, I end up calling several functions dozens of times each, whereas I only need it done once. Is there a way to capture the results of the function call?

Bear in mind that set @avar = func1(a,b,c) does not work, since I need the function called once per row of table.

Another option that I'm considering is using a subquery, like

SELECT t.result1, func2(t.d,t.e,t.f,t.result1) FROM (select *,func1(a,b,c) AS result1 FROM table) AS t

But I'm going to end up with many many layers of subqueries.


Is there anything better? I'm writing T-SQL on MS SQL Server 2005

[dglienna]

LINQ to SQL could create functions like that. Search for it. There are dozens of sample apps. (if you're using VB or C#)

Found the new link to it:

http://msdn.microsoft.com/en-us/library/bb425822.aspx

[Alsvha]

I would think Common Table Expressions can help you with your issue.

Although I've not tried it, I would think something like this (this is pseudo-code ish, don't copy directly, but use as inspiration)

WITH CTE AS (
SELECT * from func1(a,b,c) )
SELECT result1, func2(func2(d,e,f,result1)
from CTE