User Defined Functions in SQL Server

Like programming languages SQL Server also provides User Defined Functions (UDFs). From SQL Server 2000 the UDF feature was added. UDF is a programming construct that accepts parameters, does actions and returns the result of that action. The result either is a scalar value or result set. UDFs can be used in scripts, Stored Procedures, triggers and other UDFs within a database.

Benefits of UDF

1. UDFs support modular programming. Once you create a UDF and store it in a database then you can call it any number of times. You can modify the UDF independent of the source code.
2. UDFs reduce the compilation cost of T-SQL code by caching plans and reusing them for repeated execution.
3. They can reduce network traffic. If you want to filter data based on some complex constraints then that can be expressed as a UDF. Then you can use this UDF in a WHERE clause to filter data.

Types of UDF

1. Scalar Functions
2. Table Valued Functions

Consider the following Student and Subject tables for examples.

1. Scalar Functions

A Scalar UDF accepts zero or more parameters and return a single value. The return type of a scalar function is any data type except text, ntext, image, cursor and timestamp. Scalar functions can be use in a WHERE clause of the SQL Query.

Crating Scalar Function

To create a scalar function the following syntax is used.

CREATE FUNCTION  function-name (Parameters)
RETURNS  return-type
AS
BEGIN
    Statement 1
    Statement 2
             .
             .
    Statement n
    RETURN return-value
END

Example

Create a function as follows.

CREATE FUNCTION GetStudent(@Rno INT)
 RETURNS VARCHAR(50)
 AS
BEGIN
    RETURN (SELECT Name FROM Student WHERE Rno=@Rno)
END

To execute this function use the following command.

PRINT dbo.GetStudent(1)

Output: Ram

1. Table Valued Functions

A Table Valued UDF accepts zero or more parameters and return a table variable. This type of function is special because it returns a table that you can query the results of a join with other tables. A Table Valued function is further categorized into an “Inline Table Valued Function” and a “Multi-Statement Table Valued Function”.

A. Inline Table Valued Function

An Inline Table Valued Function contains a single statement that must be a SELECT statement. The result of the query becomes the return value of the function. There is no need for a BEGIN-END block in an Inline function.

Crating Inline Table Valued Function

To create a scalar function the following syntax is used.

CREATE FUNCTION function-name (Parameters)
RETURNS return-type
AS
RETURN

Query

Example

CREATE FUNCTION GetAllStudents(@Mark INT)
RETURNS TABLE
AS
RETURN
    SELECT *FROM Student WHERE Marks>=@Mark

To execute this function use the following command.

SELECT *FROM GetAllStudents(60)

Output



B. Multi-Statement Table Valued Function

A Multi-Statement contains multiple SQL statements enclosed in BEGIN-END blocks. In the function body you can read data from databases and do some operations. In a Multi-Statement Table valued function the return value is declared as a table variable and includes the full structure of the table to be returned. The RETURN statement is without a value and the declared table variable is returned.

Crating Multi-Statement Table Valued Function

To create a scalar function the following syntax is used.

CREATE FUNCTION  function-name (Parameters)
RETURNS @TableName TABLE
(Column_1 datatype,
    .
    .
 Column_n datatype
)
AS
BEGIN
Statement 1
        Statement 2
              .
              .
        Statement n
        RETURN
        END

Example

Crate a function as follows.

CREATE FUNCTION GetAvg(@Name varchar(50))
RETURNS @Marks TABLE
(Name VARCHAR(50),
 Subject1 INT,
         Subject2 INT,
 Subject3 INT,
 Average DECIMAL(4,2)
)
AS
BEGIN
            DECLARE @Avg DECIMAL(4,2)
            DECLARE @Rno INT
            INSERT INTO @Marks (Name)VALUES(@Name)
            SELECT @Rno=Rno FROM Student WHERE Name=@Name
SELECT @Avg=(Subject1+Subject2+Subject3)/3 FROM Subjects WHERE Rno=@Rno

            UPDATE @Marks SET
Subject1=(SELECT Subject1 FROM Subjects WHERE Rno=@Rno),
            Subject2=(SELECT Subject2 FROM Subjects WHERE Rno=@Rno),
            Subject3=(SELECT Subject3 FROM Subjects WHERE Rno=@Rno),
            Average=@Avg
            WHERE Name=@Name
RETURN
END

To execute this function use the following command.

SELECT * FROM GetAvg('Ram')

Output