Function: [AdventureWorks2016CTP3].[dbo].[ufnGetContactInformation]

CollapseAll image

Collapse image Function properties


 Name   Value 
 Schema   [dbo] 
 Owner   [dbo] 
 Creation date   16.11.2015 
 Type   Table 
 Is schema bound   
 Is deterministic   
 Encrypted   
 Implementation type   Transact SQL 
 ID   1373247947 

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image Parameters


Name  Description DataType  Max length  ReadOnly
@PersonID   int 4  

Collapse image Recordset returned


Name  Datatype  Max length 
PersonID int 4
FirstName nvarchar 50
LastName nvarchar 50
JobTitle nvarchar 50
BusinessEntityType nvarchar 50

Collapse image Objects that [dbo].[ufnGetContactInformation] depends on


Object name Object type Dep level
[Person].[BusinessEntityContact] Table 1
[Person].[ContactType] Table 1
[Sales].[Customer] Table 1
[HumanResources].[Employee] Table 1
[Person].[Person] Table 1
[Sales].[Store] Table 1
[Purchasing].[Vendor] Table 1
Total 7 object(s)

Collapse image Extended properties


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

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

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

Collapse image See also


List of functions