Stored Procedures versus UDFs

System and user stored procedures are managed by SQL Server in a similar fashion. They can be accessed in the same way through the system tables, such as sysobjects and syscomments. UDFs are stored in the system tables; in-built system functions are not. As a result, the code for system functions is not contained in any system table; therefore, we cannot access them, unlike system stored procedures where we can learn much from the way Microsoft has implemented them.

UDFs represent new functionality, so you may find that some of your stored procedures can be altered to functions, which will make them more accessible to your T-SQL code. Converting our system stored procedures to functions, in the right cases, will make them friendlier and easier to use, and will also reduce the lines of code in the T-SQL statements. It may also make our T-SQL less complex, as we won't be returning our data to a temporary local variable and then operating upon it.

Also, having the ability to return a table directly into another set of T-SQL is a great advantage, as it reduces the lines of code and awkward processing otherwise required to get a set of data returned from a called stored procedure to be included with the calling procedure.

Stored procedures can create temporary tables but this is not possible from within the body of a function. Also functions cannot perform any data modification operations on the permanent tables. This is not the aim of functions; data modification should remain in stored procedures.

As with stored procedures, if we placed UDFs into the SQL Server master database and prefix their name with sp_, then these functions will be available to all other databases within the installation. If you do write a function that you wish to be system -wide then stick to SQL Server's naming conventions of using all lower case, prefixed with fn_, and owned by the system_function_schema and not dbo.

To create functions owned by system_function_schema you must run the following command from the master database first, and then build your function in the master database:

     EXEC sp_configure 'allow updates', 1