Hello everyone Im having some difficulty with this question and have very little

Question

Hello everyone Im having some difficulty with this question and have very little of the code written before I ran into some problems , using adventureworks 2014 database with SQL

Create a Word document that includes the SQL query code used to explore the database tables and construct a complex function called "ContactList." Access seven tables for this function using multiple joins. Layout is an important consideration, particularly for debugging if errors are encountered. The function should call BusinessEntityID, Employee (Last + First Name), Job Title, Department, Type (Cell, Work, etc.), Phone Number, and Email Address. Run the function once you have completed it

any help would be appreciated

Explanation / Answer

we already have this function available in adventureworks for which i have modified accordingto your requirement.

create FUNCTION [dbo].[GetContactInformation](@PersonID int)
RETURNS @retContactInformation TABLE --this indicates we are going to return table which is termed as Table-Valued function
(
    -- Columns returned by the function
    [PersonID] int NOT NULL,
    [FirstName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
[JobTitle] [nvarchar](50) NULL,
    [BusinessEntityType] [nvarchar](50) NULL,
EmailAddress [nvarchar](50) NULL,
PhoneNumber [nvarchar](50) NULL
)
AS
BEGIN
IF @PersonID IS NOT NULL --function will execute only when personid is not null
  BEGIN
  IF EXISTS(SELECT * FROM [HumanResources].[Employee] e
     WHERE e.[BusinessEntityID] = @PersonID) --checking if parameter personid value in function exists in tables

  --Thereare multiple type of contact details available with in the DB such as Employee, Vendor, Store and Consumer. we also have multiple contacts for a single person
  

   --if the person belongs to Employee then this table will be returned otherwise empty dataset will be returned and proceeded with next set of queries
   INSERT INTO @retContactInformation--if the person belongs to Employee then insert the details of Employee
    SELECT @PersonID, p.FirstName, p.LastName, e.[JobTitle], 'Employee',email.EmailAddress,phone.PhoneNumber
    FROM [HumanResources].[Employee] AS e
     INNER JOIN [Person].[Person] p
     ON p.[BusinessEntityID] = e.[BusinessEntityID]
     LEFT JOIN [Person].[EmailAddress] email
     on email.BusinessEntityID=e.BusinessEntityID
     LEFT JOIN [Person].[PersonPhone] phone
     on phone.BusinessEntityID=e.BusinessEntityID
    WHERE e.[BusinessEntityID] = @PersonID;-- if the personid parameter is not matched with Employee then this will return empty dataset

  --if the person belongs to Vendor then this table will be returned otherwise empty dataset will be returned and proceeded with next set of queries
  IF EXISTS(SELECT * FROM [Purchasing].[Vendor] AS v
     INNER JOIN [Person].[BusinessEntityContact] bec
     ON bec.[BusinessEntityID] = v.[BusinessEntityID]
     WHERE bec.[PersonID] = @PersonID)--if the person belongs to Vendor then insert the details of Employee
   INSERT INTO @retContactInformation
    SELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Vendor Contact' ,email.EmailAddress,phone.PhoneNumber
    FROM [Purchasing].[Vendor] AS v
     INNER JOIN [Person].[BusinessEntityContact] bec
     ON bec.[BusinessEntityID] = v.[BusinessEntityID]
     INNER JOIN [Person].ContactType ct
     ON ct.[ContactTypeID] = bec.[ContactTypeID]
     INNER JOIN [Person].[Person] p
     ON p.[BusinessEntityID] = bec.[PersonID]
     LEFT JOIN [Person].[EmailAddress] email
     on email.BusinessEntityID=v.BusinessEntityID
     LEFT JOIN [Person].[PersonPhone] phone
     on phone.BusinessEntityID=v.BusinessEntityID
    WHERE bec.[PersonID] = @PersonID;-- if the personid parameter is not matched with Vendor then this will return empty dataset
  
  --if the person belongs to Store then this table will be returned otherwise empty dataset will be returned and proceeded with next set of queries
  IF EXISTS(SELECT * FROM [Sales].[Store] AS s
     INNER JOIN [Person].[BusinessEntityContact] bec
     ON bec.[BusinessEntityID] = s.[BusinessEntityID]
     WHERE bec.[PersonID] = @PersonID)
   INSERT INTO @retContactInformation--if the person belongs to Store then insert the details of Employee
    SELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Store Contact' ,email.EmailAddress,phone.PhoneNumber
    FROM [Sales].[Store] AS s
     INNER JOIN [Person].[BusinessEntityContact] bec
     ON bec.[BusinessEntityID] = s.[BusinessEntityID]
     INNER JOIN [Person].ContactType ct
     ON ct.[ContactTypeID] = bec.[ContactTypeID]
     INNER JOIN [Person].[Person] p
     ON p.[BusinessEntityID] = bec.[PersonID]
     LEFT JOIN [Person].[EmailAddress] email
     on email.BusinessEntityID=s.BusinessEntityID
     LEFT JOIN [Person].[PersonPhone] phone
     on phone.BusinessEntityID=s.BusinessEntityID
    WHERE bec.[PersonID] = @PersonID;-- if the personid parameter is not matched with Store then this will return empty dataset

  --if the person belongs to Consumer then this table will be returned otherwise empty dataset will be returned and proceeded with next set of queries
  IF EXISTS(SELECT * FROM [Person].[Person] AS p
     INNER JOIN [Sales].[Customer] AS c
     ON c.[PersonID] = p.[BusinessEntityID]
     WHERE p.[BusinessEntityID] = @PersonID AND c.[StoreID] IS NULL)
   INSERT INTO @retContactInformation--if the person belongs to Consumer then insert the details of Employee
    SELECT @PersonID, p.FirstName, p.LastName, NULL, 'Consumer' ,email.EmailAddress,phone.PhoneNumber
    FROM [Person].[Person] AS p
     INNER JOIN [Sales].[Customer] AS c
     ON c.[PersonID] = p.[BusinessEntityID]
     LEFT JOIN [Person].[EmailAddress] email
     on email.BusinessEntityID=p.BusinessEntityID
     LEFT JOIN [Person].[PersonPhone] phone
     on phone.BusinessEntityID=p.BusinessEntityID
     WHERE p.[BusinessEntityID] = @PersonID AND c.[StoreID] IS NULL; -- if the personid parameter is not matched with Consumer then this will return empty dataset
  END

RETURN;
END;

select * from [ufnGetContactInformation](3)

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.