Why Use User-Defined Functions?

The main benefit of user-defined functions is that you are no longer limited to just the functions that SQL Server provides. For example, the getdate() function returns both a date component and a time component, with a time down to the milliseconds. What if you wanted it to return just the date with the time always set to midnight? In that case, you would have to pass the result from getdate() through some other functions to zero out the time component. The following is a possible solution:

select convert(datetime, convert(char(10), getdate(), 110)) 

Any time you want just the date with the time always set to midnight, you have to perform the same conversion operation. Alternatively, you can create a user-defined function that performs the operations on a date value and returns the date with a time value of midnight. You could then use that function in your select lists, set clauses of update statements, values clauses for inserts, as default values, and so on, instead of having to perform the more complex conversion each time. For example, the following query uses a user-defined function, getdateonly(), to return the current date with a time of midnight:

select dbo.getdateonly(getdate()) 

The following examples show how you can use the function in other statements:

       OrderID int IDENTITY (1, 1) NOT NULL Primary Key,
       CustomerID nchar (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
       EmployeeID int NULL ,
       OrderDate datetime NULL default dbo.getdateonly(getdate()),
       RequiredDate datetime NULL ,
       ShippedDate datetime NULL

insert Orders (CustomerID, EmployeeID, RequiredDate)
    values ('BERGS', 3, dbo.getdateonly(getdate() + 7))

update Orders
     set ShippedDate = dbo.getdateonly(getdate())
     where OrderID = 1

select OrderDate,
    from Orders
  where OrderDate = dbo.getdateonly(getdate())

OrderDate                  RequiredDate               ShippedDate
-------------------------- -------------------------- --------------------------
2001-05-23 00:00:00.000    2001-05-30 00:00:00.000    2001-05-23 00:00:00.000

If you use the new getdateonly() function consistently, it makes it easier to search against datetime values because you don't have to concern yourself with the time component. Without the getdateonly() function, you might have to write the previous query as follows to make sure you find all records for a particular day:

select OrderDate, 
    from Orders
  where OrderDate >= convert(varchar(10), getdate(), 110)
    and OrderDate < convert(varchar(10), getdate() + 1, 110)

See how much easier the user-defined function makes things?

User-defined functions can accept 0?1024 input parameters, but can return only a single result: either a scalar value or a table result. Functions that return a table can be used in queries anywhere a table can be used, including joins, subqueries, and so on. The following are a couple examples of using a table-valued function that returns a list of valid book types:

select * from dbo.valid_book_types() 
insert titles
select * from newtitles
where type in (select * from dbo.valid_book_types())

Essentially, you have reduced a query to a simple function that you can now use anywhere a table can be referenced.

With a few restrictions, which will be covered later in this chapter in the "Creating Functions" section, you can write all types of functions in SQL Server to perform various calculations or routines. For example, you could write a more robust soundex() function, a function to return the factorial of a number, an address comparison function, a function that returns a valid list of code values, a function to determine the number of days that items are backordered, a function to return the average price of all books, and so on. The possibilities are nearly endless, and user-defined functions significantly increase the capabilities and flexibility of Transact-SQL.

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