MSSQL UDFs Scalar functions

USER DEFINED FUNCTIONS (UDFs)

UDFs allow us to write custom line-of-business code to be used in queries.

Kinds of UDFs.

1-Scalar UDFs.

2-Table-values UDFs.

1-Scalar UDFs.

Scalar functions may or may not have parameters, but always return a single (scalar) value. The returned value can be of any data type.

To create a function, we use the following syntax:

CREATE FUNCTION  Function_Name (@Parameter1 DataType, @Parameter2   

                                DataType,……..@Parametern Datatype)

RETURNS Return_Datatype
AS
BEGIN
    Function Body
    Return Return_Datatype
END

2- Table Values UDFs

It is further divided in to two more types

a-      Inline Table Valued function(ILTVF)

b-      Multi-statement Table Valued function(MSTVF)

a-Inline Table Valued function(ILTVF)

Syntax for creating an inline table valued function

CREATE FUNCTION Function_Name(@Param1 DataType, @Param2 DataType..., @ParamN DataType)
RETURNS TABLE
AS
RETURN (Select_Statement) 

b-Multi-statement Table Valued function(MSTVF):

Create Function fn_MSTVF ()
Returns @Table Table C1 DataType, C2 Data Type)
as
Begin
 Insert into @Table
 Select C1,C2
 From Table
 
 Return
End

1-Scalar UDFs.

Without parameters

CREATE FUNCTION GetCurrentServerDate()

RETURNS VARCHAR(20)

AS

BEGIN

      RETURN CONVERT(VARCHAR(20), SYSDATETIME(), 107)

END

GO

SELECT dbo.GetCurrentServerDate() as GetCurrentServerDate

GetCurrentServerDate

Jun 07, 2016

With Parameters

write a function to claculate the age.   

     

CREATE FUNCTION calculateAge(@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           

Select dbo.calculateAge('10/08/1981') as age

age

34

We can also use this function  calculateAge(@DOB Date) to claculate the age of the employee in a company.

AdventureWorks2008

Select top 5 NationalIDNumber,JobTitle,MaritalStatus,Gender,BirthDate,

from HumanResources.Employee

NationalIDNumber	JobTitle	MaritalStatus	Gender	BirthDate
295847284	Chief Executive Officer	S	M	1959-03-02
245797967	Vice President of Engineering	S	F	1961-09-01
509647174	Engineering Manager	M	M	1964-12-13
112457891	Senior Tool Designer	S	M	1965-01-23
695256908	Design Engineer	M	F	1942-10-29

select top 5 NationalIDNumber,JobTitle,MaritalStatus,Gender,BirthDate,

   dbo.calculateAge(BirthDate) as age

from HumanResources.Employee

NationalIDNumber	JobTitle	MaritalStatus	Gender	BirthDate	age
295847284	Chief Executive Officer	S	M	1959-03-02	57
245797967	Vice President of Engineering	S	F	1961-09-01	54
509647174	Engineering Manager	M	M	1964-12-13	51
112457891	Senior Tool Designer	S	M	1965-01-23	51
695256908	Design Engineer	M	F	1942-10-29	73

We can also write the function to calculate the total job experience of the employee

CREATE FUNCTION calculateTotalJOb(@HD Date) 

RETURNS INT 

AS 

BEGIN 

 DECLARE @TotalYear INT 

 SET @TotalYear = DATEDIFF(YEAR, @HD, GETDATE()) -

 CASE

 WHEN (MONTH(@HD) > MONTH(GETDATE()))

  OR

 (MONTH(@HD) = MONTH(GETDATE()) AND DAY(@HD) > DAY(GETDATE()))

  THEN 1 ELSE 0 END 

 RETURN @TotalYear 

END

select top 5 NationalIDNumber,JobTitle,MaritalStatus,Gender,

      HireDate,

      dbo.calculateTotalJOb(HireDate) as TotalJob

from HumanResources.Employee

NationalIDNumber	JobTitle	MaritalStatus	Gender	HireDate	TotalJob
295847284	Chief Executive Officer	S	M	1999-02-15	17
245797967	Vice President of Engineering	S	F	1998-03-03	18
509647174	Engineering Manager	M	M	1997-12-12	18
112457891	Senior Tool Designer	S	M	1998-01-05	18
695256908	Design Engineer	M	F	1998-02-06	18

Use the following code to get the totaljob experience is grater then 18.

select NationalIDNumber,JobTitle,MaritalStatus,Gender,

      HireDate,

      dbo.calculateTotalJOb(HireDate) as TotalJob

from HumanResources.Employee

where dbo.calculateTotalJOb(HireDate)>18

---write a function to get the email address for the specific employee ------

SELECT top 5 BusinessEntityID,EmailAddress

            FROM Person.EmailAddress order by  BusinessEntityID asc

BusinessEntityID	EmailAddress
1	ken0@adventure-works.com
2	terri0@adventure-works.com
3	roberto0@adventure-works.com
4	rob0@adventure-works.com
5	gail0@adventure-works.com

CREATE FUNCTION PersonEmail(@BusinessEntityID INT)

RETURNS VARCHAR(250)               

AS

BEGIN

      RETURN (

            SELECT EmailAddress

            FROM Person.EmailAddress

            WHERE BusinessEntityID= @BusinessEntityID

      )

END

select dbo.PersonEmail(1) as emailaddress

emailaddress

ken0@adventure-works.com

---write the function to display the detail hiring date of the employee ----

CREATE FUNCTION fnLongDate(@MyDate AS DATETIME)

RETURNS VARCHAR(MAX)

AS

BEGIN

RETURN DATENAME(DW,@MyDate) + ' ' +

DATENAME(D,@MyDate) + ' ' +

DATENAME(M,@MyDate) + ' ' +

DATENAME(YY,@MyDate)

END

SELECT top 5 NationalIDNumber,JobTitle,HireDate

,dbo.fnLongDate(HireDate)as DetailDate

FROM

HumanResources.Employee

NationalIDNumber	JobTitle	HireDate	DetailDate
295847284	Chief Executive Officer	1999-02-15	Monday 15 February 1999
245797967	Vice President of Engineering	1998-03-03	Tuesday 3 March 1998
509647174	Engineering Manager	1997-12-12	Friday 12 December 1997
112457891	Senior Tool Designer	1998-01-05	Monday 5 January 1998
695256908	Design Engineer	1998-02-06	Friday 6 February 1998