Rewriting Stored Procedures as Functions

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 procedure logic is expressible in a single SELECT statement; however, it is written as a stored procedure, rather than a view, because of the need for it to be parameter driven.

• The stored procedure does not perform update operations on tables, except against table variables .

• The need for dynamic SQL statements executed via the EXECUTE statement or sp_executesql does not exist.

• The stored procedure returns no more than a single result set.

• If the stored procedure returns a result set, its primary purpose is to build an intermediate result that is typically loaded into a temporary table, which is then queried in a SELECT statement.

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