>Hey all
>
>I've inherited a system with SQL stored procedures and functions. I want to clarify my understanding.
>
>Would this ...
>
>select @startdate = dbo.myfunction(@startdate)
>select @enddate = dbo.myfunction(@enddate)
>
>SELECT * from table where datefield between @startdate and @enddate
>
>be better than this...
>
>SELECT * from table where datefield between dbo.myfunction(@startdate) and dbo.myfunction(@enddate)
No. From BOL as is:
"User-defined Function Benefits
The benefits of using user-defined functions in SQL Server are:
They allow modular programming.
You can create the function once, store it in the database, and call it any number of times in your program. User-defined functions can be modified independently of the program source code.
They allow faster execution.
Similar to stored procedures, Transact-SQL user-defined functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions. This means the user-defined function does not need to be reparsed and reoptimized with each use resulting in much faster execution times.
CLR functions offer significant performance advantage over Transact-SQL functions for computational tasks, string manipulation, and business logic. Transact-SQL functions are better suited for data-access intensive logic.
They can reduce network traffic.
An operation that filters data based on some complex constraint that cannot be expressed in a single scalar expression can be expressed as a function. The function can then invoked in the WHERE clause to reduce the number or rows sent to the client. "
Cetin