MSSQL Inline Table Valued function(ILTVF)

Inline Table Valued function(ILTVF)

Write an ILTVF without the parameter. Here we want to gather the information of the email address and National Id number from two different tables.

CREATE FUNCTION dbo.MemberEmail()

RETURNS TABLE

AS

RETURN

   SELECT e.BusinessEntityID,e.NationalIDNumber,e.JobTitle,

          a.EmailAddress

     FROM  HumanResources.Employee e

     JOIN  Person.EmailAddress a

     ON e.BusinessEntityID=a.BusinessEntityID

select * from dbo.MemberEmail()

BusinessEntityID	NationalIDNumber	JobTitle	EmailAddress
1	295847284	Chief Executive Officer	ken0@adventure-works.com
2	245797967	Vice President of Engineering	terri0@adventure-works.com
3	509647174	Engineering Manager	roberto0@adventure-works.com
4	112457891	Senior Tool Designer	rob0@adventure-works.com
5	695256908	Design Engineer	gail0@adventure-works.com

Write an ILTVF with the parameter to get the email address w.r.t gender

CREATE FUNCTION dbo.Gender(@Gender nchar(1))

RETURNS TABLE

AS

RETURN

   SELECT*

     FROM HumanResources.Employee

     where @Gender=Gender

   

 Select * from dbo.Gender('m')

Now we use the above function to get the name, email address w.r.t gender and here we using three tables.

select top 5 h.Gender,

         e.EmailAddress,

         p.FirstName

         from dbo.Gender('m') h  

         join Person.EmailAddress e

         on h.BusinessEntityID=e.BusinessEntityID

         join Person.Person p

        on  p.BusinessEntityID=e.BusinessEntityID

Gender	EmailAddress	FirstName
M	ken0@adventure-works.com	Ken
M	roberto0@adventure-works.com	Roberto
M	rob0@adventure-works.com	Rob
M	jossef0@adventure-works.com	Jossef
M	dylan0@adventure-works.com	Dylan

       

  -----------------------------------------------------------

 

 

 select top 5 h.Gender,

         e.EmailAddress,

         p.FirstName

         from dbo.Gender('f') h  

         join Person.EmailAddress e

         on h.BusinessEntityID=e.BusinessEntityID

         join Person.Person p

        on  p.BusinessEntityID=e.BusinessEntityID     

Gender	EmailAddress	FirstName
F	terri0@adventure-works.com	Terri
F	gail0@adventure-works.com	Gail
F	diane1@adventure-works.com	Diane
F	gigi0@adventure-works.com	Gigi
F	janice0@adventure-works.com	Janice

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