User-Defined Functions

Microsoft SQL Server 2000 lets you create user-defined functions.  Like any function, a user-defined function is a routine that returns a value.  Based on what kind of value it returns, each user-defined function falls into one of three categories:

• Functions that return an updateable table of data

  If a user-defined function contains a single SELECT statement and that statement is updateable, then the tabular result returned by the function is also updateable.

• Functions that return a non-updateable table of data

  If a user-defined function contains more than one SELECT statement, or contains a SELECT statement that is not updateable, then the tabular result returned by that function is not updateable.

• Functions that return a scalar value

  A user-defined function can return a scalar value.

If a function returns a table, you can use that function in the FROM clause of a query.  For more information, see Using Something Else In Place of a Table.  If a function returns a scalar value, you can use it in a query anywhere you would use a column name.  For more information, see Expressions in Queries.