In this Article we will learn about User-Defined Functions (UDFs) in Sql Server. All the examples in this article uses the pubs database.

There are three Types of UDFS in Sql Server:
1. Scalar
2. Inline Table-Valued
3. Multi-statement Table-Valued

Let us go through each of these with examples:

1.  Scalar User-Defined Function

A Scalar UDF can accept 0 to many input parameter and will return a single value. A Scalar user-defined function returns one of the scalar (int, char, varchar etc) data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages.

Example 1: Here we are creating a Scalar UDF AddTwoNumbers which accepts two input parameters @a and @b and returns output as the sum of the two input parameters.

CREATE FUNCTION AddTwoNumbers
(
@a int,
@b int
)
RETURNS int
AS
BEGIN
RETURN @a + @b
END

Once the above function is created we can use this function as below:

PRINT dbo.AddTwoNumbers(10,20)

--OR

SELECT dbo.AddTwoNumbers(30,20)

Note: For Scalar UDFS we need to use Two Part Naming Convention i.e. in the above two statements we are using dbo.AddTwoNumbers.

Whether Below statement is correct? No, because it is not using two-part naming convention. Try executing the below statement it will error out…

PRINT AddTwoNumbers(10,20)

2.  Inline Table-Valued User-Defined Function

An inline table-valued function returns a variable of data type table whose value is derived from a single SELECT statement. Since the return value is derived from the SELECT statement, there is no BEGIN/END block needed in the CREATE FUNCTION statement. There is also no need to specify the table variable name (or column definitions for the table variable) because the structure of the returned value is generated from the columns that compose the SELECT statement. Because the results are a function of the columns referenced in the SELECT, no duplicate column names are allowed and all derived columns must have an associated alias.

Example: In this example we are creating a Inline table-valued function GetAuthorsByState which accepts state as the input parameter and returns firstname and lastname  of all the authors belonging to the input state.

USE PUBS
GO

CREATE FUNCTION GetAuthorsByState
( @state char(2) )
RETURNS table
AS
RETURN (
SELECT au_fname, au_lname
FROM Authors
WHERE state=@state
)
GO

We can use the below statement to get all the authors in the state CA.

SELECT * FROM GetAuthorsByState('CA')

3. Multi-statement Table-Valued User-Defined Function

A Multi-Statement Table-Valued user-defined function returns a table. It can have one or more than one T-Sql statement. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, we can use it in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.

Example: In this example we are creating a Multi-Statement Table-Valued function GetAuthorsByState which accepts state as the input parameter and returns author id and firstname of all the authors belonging to the input state. If for the input state there are no authors then this UDF will return a record with no au_id column value and firstname as ‘No Authors Found’.

USE PUBS
GO

CREATE FUNCTION GetAuthorsByState
( @state char(2) )
RETURNS
@AuthorsByState table (
au_id Varchar(11),
au_fname Varchar(20)
)
AS
BEGIN

INSERT INTO @AuthorsByState
SELECT  au_id,
au_fname
FROM Authors
WHERE state = @state

IF @@ROWCOUNT = 0
BEGIN
INSERT INTO @AuthorsByState
VALUES ('','No Authors Found')
END

RETURN
END
GO

We can use the below statements to get all the authors in the given input state:

SELECT * FROM GetAuthorsByState('CA')
SELECT * FROM GetAuthorsByState('XY')