User Defined Functions

• 1. Scalar functions
• 2. Inline table-valued functions
• 3. Multistatement table-valued functions
• 
  
• Scalar functions may or may not have parameters, but always return a single (scalar) value. The returned value can be of any data type, except text, ntext, image, cursor, and timestamp.
• 
  
• Let us now create a function which calculates and returns the age of a person. To compute the age we require, date of birth. So, let's pass date of birth as a parameter. So, AGE() function returns an integer and accepts date parameter.
• CREATE FUNCTION Age(@DOB Date)  
• RETURNS INT  
• AS  
• BEGIN  
•  DECLARE @Age INT  
•  SET @Age = DATEDIFF(YEAR, @DOB, GETDATE()) - CASE WHEN(MONTH(@DOB) > MONTH(GETDATE())) OR (MONTH(@DOB) =MONTH(GETDATE()) AND DAY(@DOB) > DAY(GETDATE())) THEN 1 ELSE 0 END  
•  RETURN @Age  
• END
• 
  
• When calling a scalar user-defined function, you must supply a two-part name,OwnerName.FunctionName. dbo stands for database owner.
• Select dbo.Age( dbo.Age('10/08/1982')
• 
  
• You can also invoke it using the complete 3 part name, DatabaseName.OwnerName.FunctionName.
• Select SampleDB.dbo.Age('10/08/1982')
• 
  
• Consider the Employees table below.
•  
• 
  
• Scalar user defined functions can be used in the Select clause as shown below.
• Select Name, DateOfBirth, dbo.Age(DateOfBirth) as Age from tblEmployees
• 
• 
  
• Scalar user defined functions can be used in the Where clause, as shown below.
• Select Name, DateOfBirth, dbo.Age(DateOfBirth) as Age 
• from tblEmployees
• Where dbo.Age(DateOfBirth) > 30
•  
• 
  
• 
  
• A stored procedure also can accept DateOfBirth and return Age, but you cannot use stored procedures in a select or where clause. This is just one difference between a function and a stored procedure. There are several other differences, which we will talk about in a later session.
• 
  
• To alter a function we use ALTER FUNCTION FuncationName statement and to delete it, we use DROP FUNCTION FuncationName.
• 
  
• Inline table valued functions : it is a function which we can use to join the tables 
• because this function can return a table.
• 
  
• 
  
• Syntax for creating an inline table valued function
• CREATE FUNCTION Function_Name(@Param1 DataType, @Param2 DataType..., @ParamN DataType)
• RETURNS TABLE
• AS
• RETURN (Select_Statement) 
• 
  
• 
  
• If you look at the way we implemented this function, it is very similar to SCALAR function, with the following differences
• 1. We specify TABLE as the return type, instead of any scalar data type
• 2. The function body is not enclosed between BEGIN and END block. Inline table valued function body, cannot have BEGIN and END block.
• 3. The structure of the table that gets returned, is determined by the SELECT statement with in the function.
• 
  
• 
• 
  
• 
  
• Where can we use Inline Table Valued functions
• 1. Inline Table Valued functions can be used to achieve the functionality of parameterized views. We will talk about views, in a later session.
• 2. The table returned by the table valued function, can also be used in joins with other tables.
• 
  
• Consider the Departments Table
• 
• 
  
• Joining the Employees returned by the function, with the Departments table
• Select Name, Gender, DepartmentName 
• from fn_EmployeesByGender('Male') E
• Join tblDepartment D on D.Id = E.DepartmentId
• 
  
• Executing the above query should produce the following output 
• 
• 
  
• 
  
• Multi-statement Table Valued function(MSTVF):
• Create Function fn_MSTVF_GetEmployees()
• Returns @Table Table (Id int, Name nvarchar(20), DOB Date)
• as
• Begin
• Insert into @Table
• Select Id, Name, Cast(DateOfBirth as Date)
• From tblEmployees
• Return
• End
• 
  
• Calling the Inline Table Valued Function:
• Select * from fn_ILTVF_GetEmployees()
• 
  
• Calling the Multi-statement Table Valued Function:
• Select * from fn_MSTVF_GetEmployees()
• 
  

1.