User defined Functions in SQL

User Defined Functions are compact pieces of Transact SQL code, which can accept parameters, and return either a value, or a table. Data transformation and reference value retrieval are common uses for functions. User Defined Functions enable the developer or DBA to create functions of their own, and save them inside SQL Server.

Advantages of User Defined Functions

Before SQL 2000, User Defined Functions (UDFs), were not available. Stored Procedures were often used in their place.

One of the advantages of User Defined Functions over Stored Procedures, is the fact that a UDF can be used in a Select, Where, or Case statement. They also can be used to create joins. In addition, User Defined Functions are simpler to invoke than Stored Procedures from inside another SQL statement.

Disadvantages of User Defined Functions

User Defined Functions cannot be used to modify base table information. The DML statements INSERT, UPDATE, and DELETE cannot be used on base tables.

Another disadvantage is that SQL functions that return non-deterministic values are not allowed to be called from inside User Defined Functions.

GETDATE is an example of a non-deterministic function. Every time the function is called, a different value is returned. Therefore, GETDATE cannot be called from inside a UDF you create.

Types of User Defined Functions

There are three different types of User Defined Functions. Each type refers to the data being returned by the function.
  1. Scalar functions return a single value.
  2. In Line Table functions return a single table variable that was created by a select statement.
  3. The final UDF is a Multi-statement Table Function. This function returns a table variable whose structure was created by hand, similar to a Create Table statement. It is useful when complex data manipulation inside the function is required.


Example for Scalar-valued Function


CREATE FUNCTION dbo.DateOnly
(
        @DateTime datetime
)
RETURNS varchar(10)
AS
BEGIN
        DECLARE
@Output varchar(10)
        SET @Output = CONVERT(varchar(10),@DateTime,101)
        RETURN @Output
END

To call the function, execute : SELECT dbo.DateOnly(GETDATE())



Example for Inline Table-valued Function

CREATE FUNCTION dbo.FindNamesBy
(
        @Name varchar(10)
)
RETURNS TABLE
AS
BEGIN
        RETURN SELECT * FROM
Employee
                     WHERE EmpName LIKE '%' + @Name
END

To use the above function, execute : SELECT * FROM dbo.FindNamesBy('Cherukuri')



Example for Multi statement Table-valued Function

CREATE FUNCTION dbo.MultiLineFunction
(
        @Name varchar(10)
)
RETURNS @Result TABLE
(
        Empname varchar(20),
        HireDate datetime,
        OnProbation char(1)
)
AS
BEGIN
        INSERT INTO
@Result (EmpName, HireDate)
                SELECT Empname, HireDate FROM Employee
                WHERE EmpName LIKE '%' + @Name

        UPDATE @Result SET OnProbation = 'N'

        UPDATE @Result SET OnProbation = 'Y'

        WHERE HireDate < '11/16/2002'

        RETURN
END


To use the above function, execute :
SELECT EmpName,HireDate,OnProbation FROM dbo.MultiLineFunction('Ch')