SQL Server User Defined Functions

SQL Server User Defined Functions:

Functions accepts some parameters process and then returns some data. A function can have maximum 1024 input parameters. A parameter of the function can have a default value, the keyword DEFAULT must be specified when calling the function to get the default value. UDF are used to reduce the code blocks and it makes easy to maintain the function in case it needs to change.

UDF Types:

1.  Scalar value returning UDF
2.  Table value returning UDF
     a.  Inline Table
     b.  Multistatement Table

Scalar UDFs:

These UDFs returns a single value of a scalar datatypes such as integer, varchar(n), char(n), money, datetime, bit etc. Even UDF returns User Defined Data Types (UDDTs). All the datatypes can not be returned from the UDFs like text, ntext, image, timestamp etc.

How to Create Scalar UDF:

CREATE FUNCTION fnNeedToReorder
     (
         @ReorderLevel INT,
         @UnitsInStock INT,
         @UnitsOnOrder INT
     )
    RETURNS VARCHAR(3)
AS
BEGIN
    DECLARE @ReturnValue VARCHAR(3)
    IF ((@UnitsInStock + @UnitsOnOrder) - @ReorderLevel) < 0
        SET @ReturnValue = 'Yes'
    ELSE
        SET @ReturnValue = 'No'
    RETURN @ReturnValue
END


Use Scalar UDF:

SELECT    ProductID,
      ReorderLevel,
      UnitsInStock,
      UnitsOnOrder,
      fnNeedToReorder(ReorderLevel, UnitsInStock, UnitsOnOrder) AS NeedToReorder
FROM    Products

another alternative to use of above function can be computed column in table. In products table we can create one computed column called as NeedToReorder as shown below

ALTER TABLE Products
ADD  NeedToReorder AS fnNeedToReorder(ReorderLevel,UnitsInStock, UnitsOnOrder)

Few practical use of functions:

1.  Format the phone number in desired format
2.  Format a date in standard format MM/DD/YYYY
3.  Number in particular format ex. you need 1 to 100 number like 001,002,003 .. 100 format.


Table Valued UDFs:

With these UDFs we can return either inline or multistatement tables, a rowset can be returned via the table Data Type. As I told earlier with table valued UDF two types are inline and multistatement table value returing UDFs. Inline Table returning UDF returns rowset via SLQ server table datatype. Inline table returning UDF are defined with a single SELECT statement making up the body of the function. Such UDFs cannot contain additional T-SQL logic outside of the SQL SELECT statement that defines the table it will return. It is easier to create inline table compared to multistatement table returning UDFs.

Create Inline Table Valued UDF:

CREATE FUNCTION Fn_GetEmpByCity (@City VARCHAR(30))
       RETURNS TABLE
AS
RETURN
    (
        SELECT    FirstName, LastName, Address
        FROM    Employees
        WHERE    City = @City
    )

Use of Inline UDF:

Select * from Fn_GetEmpByCity('Ganesh')