Rewriting Stored Procedures As Functions

Artikkeli
12/03/2008

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.

Jaa

Rewriting Stored Procedures As Functions

Criteria for Table-Valued Functions

Rewriting Extended Stored Procedures Using CLR

See Also

Concepts

Help and Information

Lisäresursseja