Chapter 30. User-Defined Functions

by Ray Rankins

IN THIS CHAPTER

• Why Use User-Defined Functions?

• Types of Functions

• Creating and Managing Functions

• System-Wide Table Valued Functions

• Rewriting Stored Procedures as Functions

SQL Server provides a number of predefined functions built in to Transact-SQL. (These functions are covered in more depth in Chapter 26, "Using Transact-SQL in SQL Server 2000.") The supplied functions help extend the capabilities of Transact-SQL, providing the ability to perform string manipulation, mathematical calculations, datatype conversions, and so on, within your T-SQL code. Although SQL Server provides a pretty extensive set of functions, there are always times when you wish you had a function that is not provided. You could always create a stored procedure to do custom processing, but you can't use the result of a stored procedure in a where clause or as a column in a select list. If only you could create your own functions.

In SQL Server 2000 you can, with the ability to create user-defined functions. User-defined functions can return a single scalar value like the built-in functions, or can return a result set as a table variable. (For more information on table variables, see Chapter 26.)

This chapter takes a look at how to create and use user-defined functions as well as when you might want to rewrite stored procedures as functions.