Creating Custom Functions in SQL Server 2000
- New Advances in SQL Server Functions
- User-Defined Functions
- Functions That Return a Table
- Calling User-Defined Functions
- Function Determinism
One of the newest advances in SQL Server 2000 is the ability to create custom functions. In previous versions of SQL Server, all functions were defined by the system developers and couldn't be modified in any way. In SQL Server 2000, developers can create their own functions that return any type of value. These functions can be used in stored procedures or as a column in a table as a computed column.In this chapter, I cover the following:
New enhancements to SQL Server functions
Functions that return a table
Calling user-defined functions
New Advances in SQL Server Functions
All programming languages, including T-SQL, have functions that can be used to create powerful applications. In most languages, programmers can create their own functions that allow them to expand on what the system can do. In previous versions of SQL Server, you didn't have the ability to create functions. You could create stored procedures, but you couldn't use them in certain places, such as within the context of a SELECT statement. Now SQL Server 2000 now enables you to create your own functions.
A function is simply a subroutine that encapsulates SQL statements. By creating functions, you can easily reuse the code that you have created to compare and contrast the differences between functions and stored procedures. An example of a built-in function is GETDATE, which returns the current system date. One major difference between this function and a stored procedure is that the function can be called from the context of a SELECT or other DML statement. I go over this in depth later in the chapter.