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.



    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features