Home > Articles > Data > SQL Server

Creating Custom Functions in SQL Server 2000

  • Print
  • + Share This
In previous versions of SQL Server, functions were defined by system developers and couldn't be modified in any way. SQL Server 2000, however, enables developers to create custom functions that return any type of value. Matt Shepker teaches you here how to create and use these functions.
This chapter was selected from Writing Stored Procedures for Microsoft SQL Server.
From the author of

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

  • User-defined functions

  • Functions that return a table

  • Calling user-defined functions

  • Function determinism

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.

  • + Share This
  • 🔖 Save To Your Account