Share via

  • Article

Rewriting Stored Procedures as Functions

This topic describes how to determine whether to rewrite existing stored procedure logic as user-defined functions. For example, if you want to invoke a stored procedure directly from a query, repackage the code as a user-defined function.

In general, if the stored procedure returns a, single, result set, define a table-valued function. If the stored procedure computes a scalar value, define a scalar function.

Criteria for Table-Valued Functions

If a stored procedure meets the following criteria, it is a good candidate for being rewritten as a table-valued function:

  • The logic is expressible in a single SELECT statement but is a stored procedure, rather than a view, only because of the need for parameters. This scenario can be handled with an inline table-valued function.

  • The stored procedure does not perform update operations, except to table variables.

  • There is no need for dynamic EXECUTE statements

  • The stored procedure returns one result set.

  • The primary purpose of the stored procedure is to build intermediate results that are to be loaded into a temporary table, which is then queried in a SELECT statement. INSERT...EXEC statements can be written using table-valued functions. For example, consider the following sequence:

    INSERT #temp EXEC sp_getresults
SELECT ...
FROM #temp, t1
WHERE ...

    The sp_getresults stored procedure can be rewritten as a table-valued function, for example fn_results(), which means the preceding statements can be rewritten as:

    INSERT #temp
SELECT ...
FROM fn_results(), t1
WHERE ...

Rewriting Extended Stored Procedures Using CLR

CLR functions provide a more reliable and scalable alternative to extended stored procedures. Many extended stored procedures perform some computational task that is harder to express in Transact-SQL. Such stored procedures can be rewritten using the CLR given the benefits described above. Further, extended stored procedures that return result sets by accessing an external resource such as a file or Web Service can be rewritten using a CLR table-valued function. For more information, see Creating CLR Functions.