In previous releases of SQL Server, if you wanted to do custom processing, you had to create stored procedures to do things that would have worked better as functions. For example, you couldn't use the result set of a stored procedure in a where clause or a return value as a column in a select list. Using a stored procedure to perform calculations on columns in a result set often required using a cursor to step through each row in a result set and pass the column values fetched to the stored procedure, which typically returned the computed value via an output parameter. Another alternative was to retrieve the initial result set into a temporary table and then perform additional queries/updates against the temporary table to modify the column values, which often required multiple passes . Neither of these methods were an efficient means of processing the data, but few other alternatives existed. If you needed to join against the result set of a stored procedure, you had to insert the result set into a temporary table first, and then join against the temporary table, as shown in the following code fragment:
... insert #results exec result_proc select * from other_Table join #results on other_table.pkey = #results.keyfield ...
Now that SQL Server 2000 supports user -defined functions, you might want to consider rewriting some of your stored procedures as functions to take advantage of the capabilities of functions and improve the efficiency of your SQL code. The main criteria are situations where you would like to be able to invoke the stored procedure directly from within a query. If the stored procedure returns a result-set, it is a candidate for being written as a table-valued function. If it returns a scalar value, usually via an output parameter, it is a candidate for being written as a scalar function. However, the following criteria should also be considered when deciding whether a procedure is a good candidate for being rewritten as a function:
The result_proc stored procedure, referenced earlier in this section, could possibly be rewritten as a table-valued function called fn_result(). The preceding code fragment then could be rewritten as follows :
SELECT * FROM fn_results() fn join other_table o.pkey = fn.keyfield