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: