Rewriting Stored Procedures as Functions

User-defined functions are new in SQL Server 2000. In previous releases, many applications had stored procedures to perform tasks that could have been much more naturally coded as functions, but because there was no facility for writing user-defined functions, stored procedures had to be used. If you've upgraded to SQL Server 2000 from an earlier release, you might be wondering which of your existing stored procedures you should rewrite as functions.

The following list of criteria for creating functions from stored procedures mostly duplicates what you'll find in SQL Server Books Online, but I've included it here for convenience. Your main consideration should be whether you want to be able to invoke an existing stored procedure directly from within a query. If so, you should consider re-creating the procedures as a function.

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

If a stored procedure meets the following criteria, it's 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. You can handle this scenario using 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 (discussed later in this chapter)
  • 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.


Inside Microsoft SQL Server 2000
Inside Microsoft SQL Server 2000
ISBN: 0735609985
EAN: 2147483647
Year: 2005
Pages: 179
Authors: Kalen Delaney

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net