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