SQL Query Optimization

[haleed]

Hi,

I wrote a query with some user defined functions. I want to optimize the query. What I want to do is to get the values passed by a function to a variable and use that variable value where ever that function might be repeated in the same query. How can I do this ?

Example:

SELECT Field1, Field2, Field3, Function(Field1), Function(Field2), Function(Field1) + Function(Field2)
FROM Table1

Function(Field1) and Function(Field2) needs to be assigned to a variable and then use that variable when the functions do get repeated as in the above example.

Pls help.

Thanx,
Zaharan Haleed.

[skhanal]

In SQL Server 2000, you can create a scalar function and save the result in a variable and use that later.

declare @result int

set @result = yourfunction()

select * from yourtable
where col1 = @result