CREATE FUNCTION [dbo].[ufnGetContactInformation](@PersonID int)
RETURNS @retContactInformation TABLE
(
   -- 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
)
AS
-- Returns the first name, last name, job title and business entity type for the specified contact.
-- Since a contact can serve multiple roles, more than one row may be returned.
BEGIN
   IF @PersonID IS NOT NULL
       BEGIN
       IF EXISTS(SELECT * FROM [HumanResources].[Employee] e
                   WHERE e.[BusinessEntityID] = @PersonID)
           INSERT INTO @retContactInformation
               SELECT @PersonID, p.FirstName, p.LastName, e.[JobTitle], 'Employee'
               FROM [HumanResources].[Employee] AS e
                   INNER JOIN [Person].[Person] p
                   ON p.[BusinessEntityID] = e.[BusinessEntityID]
               WHERE e.[BusinessEntityID] = @PersonID;

       IF EXISTS(SELECT * FROM [Purchasing].[Vendor] AS v
                   INNER JOIN [Person].[BusinessEntityContact] bec
                   ON bec.[BusinessEntityID] = v.[BusinessEntityID]
                   WHERE bec.[PersonID] = @PersonID)
           INSERT INTO @retContactInformation
               SELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Vendor Contact'
               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]
               WHERE bec.[PersonID] = @PersonID;
       
       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
               SELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Store Contact'
               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]
               WHERE bec.[PersonID] = @PersonID;

       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
               SELECT @PersonID, p.FirstName, p.LastName, NULL, 'Consumer'
               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;
       END

   RETURN;
END;
GO
EXEC sp_addextendedproperty N'MS_Description', N'Table value function returning the first name, last name, job title and contact type for a given contact.', 'SCHEMA', N'dbo', 'FUNCTION', N'ufnGetContactInformation', NULL, NULL
GO
EXEC sp_addextendedproperty N'MS_Description', N'Input parameter for the table value function ufnGetContactInformation. Enter a valid PersonID from the Person.Contact table.', 'SCHEMA', N'dbo', 'FUNCTION', N'ufnGetContactInformation', 'PARAMETER', N'@PersonID'
GO