dbo.ufnGetContactInformation User Defined Function

Description

Table value function returning the first name, last name, job title and contact type for a given contact.

Properties

Creation Date	27/10/2017 14:33
Encrypted
Ansi Nulls

Parameters

Parameter	Direction	Description	Data Type	Size
@PersonID	In	Input parameter for the table value function ufnGetContactInformation. Enter a valid PersonID from the Person.Contact table.	Integer	4
@TABLE_RETURN_VALUE	Return Value	Result table returned by table valued function	Empty	0

Objects that dbo.ufnGetContactInformation depends on

Database Object	Object Type	Description	Dep Level
dbo.AccountNumber	User Defined Data Type		2
Person.BusinessEntity	Table	Source of the ID that connects vendors, customers, and employees with address and contact information.	2
Person.BusinessEntityContact	Table	Cross-reference table mapping stores, vendors, and employees to people	1
Person.ContactType	Table	Lookup table containing the types of business entity contacts.	1
Person.CountryRegion	Table	Lookup table containing the ISO standard codes for countries and regions.	3
Sales.Customer	Table	Current customer information. Also see the Person and Store tables.	1
HumanResources.Employee	Table	Employee information such as salary, department, and title.	1
dbo.ErrorLog	Table	Audit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct.	3
dbo.Flag	User Defined Data Type		2
dbo.NameStyle	User Defined Data Type		2
Person.Person	Table	Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.	1
Sales.SalesPerson	Table	Sales representative current information.	2
Sales.SalesTerritory	Table	Sales territory lookup table.	2
Sales.Store	Table	Customers (resellers) of Adventure Works products.	1
dbo.ufnLeadingZeros	User Defined Function	Scalar function used by the Sales.Customer table to help set the account number.	2
dbo.uspLogError	Stored Procedure	Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.	2
dbo.uspPrintError	Stored Procedure	Prints error information about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without printing any error information.	2
Purchasing.Vendor	Table	Companies from whom Adventure Works Cycles purchases parts or other goods.	1

Procedure Source Code

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;

Related Objects

dbo Schema
AdventureWorks Database