SQL Programmers Blog - User-Defined Functions

User-Defined function (UDF) was introduced in SQL Server 2000.  UDF can be used in a complex T-SQL Query, and will be used in problems that were impossible or required cursors can now be solved with UDFs.

Advantages

• UDFs can be used in complex logic within a query
• UDFs can be used to create new functions for complex expressions.
• UDFs offers the benefits of views because they can be used in the “from clause” of the select statement. User defined functions accepts parameters whereas views cannot.
• UDFs offer the benefits of stored procedures because they are compiled and optimized in the same way.

User-Defined functions come in three types

• Scalar functions that return a single value
• Inline functions similar to views
• Multi-statement table functions

Scalar Functions

A scalar function will return a single specific value. The function can accept multiple parameters and perform calculations then return a single value. These UDFs can be used within any expressions. By using the return command we can return the value. The return command should be the last command in the user-defined function.

Restrictions

• User-Defined scalar functions are not permitted to update the databases, but they can access the temporary tables.
• They cannot return Binary Large Object (BLOB) data like ntext, text and image data type variables.

Creating a Scalar Function

User Defined Functions can be created, altered and dropped by using the DDL commands.

Syntax

CREATE FUNCTION FunctionName (Input Parameters)
RETURN DataType
 AS
BEGIN       
    CODE
    RETURN Expression
END

We will specify the data-type definition with the Input Parameters. Optionally can include the default value like stored procedure parameters.

The following user-defined function performs a simple mathematical function. The second parameter includes a default value:

Example

CREATE FUNCTION ADDITION(@A INT,@B INT = 1)
RETURNS INT
AS
    BEGIN
        RETURN @A + @B
    END
GO

In the above UDF ADDITION defined with two parameters A and B and will return the sum of the two input values. The second parameter B is optional. If no value received then the value 1 will be assigned in the parameter @B.  The next line to the Create statement “returns int” means will return integer value.

Use the following query to call the function

SELECT DBO.ADDITION(3,2)

It will return 5. (@A=3 and @B=2)

SELECT DBO.ADDITION(3,DEFAULT)

It will return 4 (@A=3 and @B=1)

In the function, second parameter is Optional. In UDF calling have to specify DEFALT for optional parameters. But in the stored procedure we will not specify anything.

Inline Table-Valued Functions

A inline Table-Valued function returns data type of table which is derived from a single SELECT statement. We no need to use begin and end statement because return values is derived from the SELECT statement.

Syntax

CREATE FUNCTION FunctionName (InputParamters)
RETURNS Table
AS
RETURN (Select Statement)

Example

CREATE FUNCTION fx_CompanyByCity
( @City nvarchar(100) )
RETURNS table
AS
RETURN (
        SELECT *
        FROM Company
        WHERE City =@City
         )
go

In the above function fx_CompanyByCity will recive a parameter value City and select all rows from company table where the city name is @City.

You can call the above function with the following statement

select * from fx_CompanyByCity('cbe')

Result

The above list will show all the records from Company Tables where the city value is ‘cbe’

Multi-Statement Table-Valued Functions

Multi-Statement Table-Valued Functions are more complicated then the other two types functions. Because this type of function uses multiple statements to build the table that is returned to the calling statement. Here table variable must be declared and defined. The following example shows how to implement Multi-Statement Table-Valued Function and get the single table variable as output.

Syntax

CREATE FUNCTION FunctionName (InputParameters)
RETURNS @TableName TABLE (Columns)
AS
BEGIN
    Code to populate table variable
    RETURN
END

Example

CREATE FUNCTION fGetCompany()
RETURNS @Company TABLE

(CompanyName VARCHAR(100),
 OrderNo VARCHAR(50),
 Address VARCHAR(100))

AS
BEGIN
INSERT @Company (CompanyName,OrderNo,Address)
    SELECT Company.CompanyName, Orders.OrderNo, Company.Address
        FROM Company
            INNER JOIN Orders
                ON Company.Id = Orders.CompanyId
                    where Company.Address='Dia Street'

INSERT @Company (CompanyName,OrderNo,Address)
    SELECT Company.CompanyName, Orders.OrderNo, Company.Address
        FROM Company
            INNER JOIN Orders
                ON Company.Id = Orders.CompanyId
                    where Company.CompanyName='Company1'
RETURN
END

The above function first creates a table variable @Company within the Create Function header.

Within the body of the function two insert statements populate the @Company table variable

When the function completes execution the @Company table variable is passed back as the output of the function.

We can call the fGetCompany() function with the following statement

SELECT * FROM DBO.fGetCompany()

Output

We careated the @Company table variable with two select statements. First select statement get the values from the Company and Order table with the conditon Company.Address='Dia Street' . Second select statement get the values from the Company and Order table with the condition Company.CompanyName='Company1'.

Here I have used simple examples to understand the User Defined function. UDFs will help you in varias complex situations.