Functions are used to calculate and return values. Unlike stored procedures and triggers, a function cannot have side-effects. That is, a user-defined function can not directly change objects outside the scope of the BEGIN...END block used to define the function body. Instead functions are used to change objects outside the scope of the function by assigning their return value.

SQL Server 2008 comes with a vast array of built-in functions. In addition, new functions can be defined using 'CREATE FUNCTION' to return either scalar or table values.

To modify a function use ALTER FUNCTION to replace the current definition with a new definition, effectively DROP-ping the function and CREATE-ing it afresh.

The body of a function definitions can contain

• DECLARE statments to define local variables
• Assignments to local variables using SET or SELECT
• Flow Control structures except TRY...CATCH
• UPDATE, INSERT and DELETE statements that modify local table variables
• EXEC statements calling extended stored procedures

Function Options

The options that can be specified in function definitions are:

ENCRYPTION

Applies a bitwise OR to the code in the function to achieve a level of obfuscation. Not a reliable security measure as the obfuscation is easily reversed

SCHEMABINDING

Ensures that you can't drop dependant objects within the function body. If you try to issue a drop statement on an object referenced by a function with option SCHEMABINDING, SQL Server will prevent the DROP statement executing.

EXECUTE AS [LOGIN | USER | CALLER]

Enables the functions to run under a different user context

RETURNS NULL ON NULL INPUT

If this option is specified, the procedure does not execute and immeadiately returns NULL, if NULL input parameters are supplied in the call

CALLED ON NULL INPUT

If this option is specified, procedure executes even if NULL input parameters are supplied in the call.

RETURNS NULL ON NULL INPUT and its opposite CALLED ON NULL INPUT are only applicable to scalar functions

Function Usage

A function in a SELECT list is called once to return a value for the resultset. If the function is applied to a table column, then the function is called for each field returned. User-defined functions can also appear in WHERE, GROUP_BY, ORDER_BY, SET, and VALUES expressions, but the function is called once for each row returned. User-defined functions can also be used in CHECK and DEFAULT constraints if the arguments passed to the function reference columns in the table.

Scalar Functions

Scalar functions return scalar values and are defined with the following general syntax:

CREATE FUNCTION function_name [@param1, @param2, ....]
RETURNS data_type
[WITH OPTIONS]
AS
  BEGIN
	function_body
  RETURN scalar_expression
  END

A simple function to calculate pii could be defined and called as follows:

CREATE FUNCTION ufn_pii(@iters int)
RETURNS FLOAT
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
DECLARE @pii FLOAT
DECLARE @numerator FLOAT
DECLARE @operator REAL
SET @operator = -1
SET @numerator = 3
SET @pii = 4.0
WHILE @iters > 0
	BEGIN
	SET @pii += @operator * (4/@numerator)
	SET @numerator += 2
	SET @operator *= -1
	SET @iters -= 1
	END
RETURN @pii
END
GO

SELECT dbo.ufn_pii(1000000)

In the above example, the user-defined function is referenced in the select_list.

The power of functions lies in their ability to perform complex tasks within a select list. Suppose we had a table that lists song titles with either the album name or an artist name.

Name                                   Song 
-------------------------------------- ---------------------------------------
The Beatles                            Penny Lane
Sgt Pepper's Lonely Hearts Club Band   A Day in the Life
Led Zeppelin                           Whole Lotta Love
Wish You Were Here                     Wish You Were Here
Steve Miller                           Gangster of Love
      .                                     .
      .                                     .
      .                                     .
      .                                     .
      .                                     .
American Beauty                        Box of Rain

If we wanted to produce a list of song titles and artist names only, we'd have to check each row to see if the name was an artist name or an album name. If we had an album name, we'd then have to lookup the artist name from an album to artist lookup table. This would make for a rather complicated SQL statement. But we can hide much of the complexity in a function:

CREATE FUNCTION ufn_lookup_artist(@name varchar(50))
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @artist VARCHAR(50)

IF EXISTS (SELECT album FROM album_artist_lookup WHERE album = @name)
BEGIN
  -- We have an album name so we need to send back the artist name instead
  SELECT @artist = a.artist FROM album_artist_lookup a 
	WHERE a.album = @name
END
ELSE 
  -- We already have an artist name so no need for a lookup
  SET @artist = @name
END
-- If our lookup went wrong, at least return the original parameter
RETURN ISNULL(@artist, @name)
END

We can then use a simple SQL statement to produce our list of songs and artists as follows:

SELECT ufn_lookup_artist(Name) as Artist, Song FROM my_music

If we wanted to go in the other direction, that is lookup an album name from an artist name, the situation becomes a little more difficult, because an artist may have more than one album. But we could do the lookup if we used both the artist name and the song title, and a lookup table containing all three:

CREATE FUNCTION ufn_lookup_album(@name varchar(50), @song varchar(50))
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @album VARCHAR(50)

IF EXISTS (SELECT artist FROM artist_album_lookup WHERE artist = @name)
BEGIN
  -- We have an artist name so we need to send back the album name instead
  SELECT @album = a.album FROM artist_album_lookup a 
	WHERE a.artist = @name AND a.song = @song
END
ELSE 
  -- We already have an album name so no need for a lookup
  SET @artist = @name
END
-- If our lookup went wrong, at least return the original parameter
RETURN ISNULL(@artist, @name)
END

This time we call the function with two parameters:

SELECT ufn_lookup_album(Name, Song) AS Album, Song FROM my_music

Inline Table Functions

An inline table function contains a single SELECT statement used to return a table-valued variable:

CREATE FUNCTION function_name [@param1, @param2, ....]
RETURNS TABLE
[WITH OPTIONS]
AS 
  RETURN select_stmt

The following example creates a function that returns a table-valued variable, containing data for Robots manufactured with the last x days:

CREATE FUNCTION dbo.ufn_builtrobots (@num_days INT)
RETURNS TABLE
WITH SCHEMABINDING, ENCRYPTION
AS 
RETURN
(SELECT Robot.RobotID, Robot.ManufactureDate, Robot.UnitPrice FROM dbo.Robot
	WHERE DATEADD(day, @num_days, ManufactureDate) >= GETDATE())
GO

SELECT * FROM dbo.ufn_builtrobots(14)

The SCHEMABINDING option will ensure that the dbo.Robot table is not dropped from the database.

Multi-Statement Table Functions

A multi-statement table-value function, returns a table-value variable, and may consist of multiple statements. The general syntax is:

CREATE FUNCTION function_name [@param1, @param2, ....]
RETURN @return_var TABLE (table_definition)
[WITH OPTIONS]
AS
  BEGIN
	function_body
  RETURN
  END

We can modify the ufn_pii function to store the calculated value of pii at each iteration in a table, which can then be called via a SELECT statement:

CREATE FUNCTION ufn_pii_table(@iters int)
RETURNS @pii_table TABLE(
  iter int not null,
  pii float not null)
AS
  BEGIN
  DECLARE @pii FLOAT
  DECLARE @numerator FLOAT
  DECLARE @operator REAL
  DECLARE @loop_counter int
  --DECLARE @pii_table TABLE
  SET @loop_counter = 1
  SET @operator = -1
  SET @numerator = 3
  SET @pii = 4.0
  WHILE @loop_counter <= @iters
  	BEGIN
  	SET @pii += @operator * (4/@numerator)
  	SET @numerator += 2
  	SET @operator *= -1
  	INSERT INTO @pii_table(iter, pii) VALUES(@loop_counter, @pii)
  	SET @loop_counter += 1
  	END
  RETURN 
  END
GO

SELECT iter, pii FROM dbo.ufn_pii_table(1000000)