# Function: ufnGetContactInformation

## Properties

Property | Value
--- | ---
Name | ufnGetContactInformation
ID | 2139154666
Schema | dbo
Owner | dbo
Execute As | Caller
Type | SQL TABLE VALUED FUNCTION
Uses Ansi Nulls |
Uses Quoted Identifier |
Is Schema Bound |
Uses Database Collation |
Null on Null Input |
Encrypted |
MS Shipped |
Published |
Schema Published |
Deterministic |
Precise |
System Verified |
System Data Access |
User Data Access |
Modify Date | 8/8/2010 5:23:46 PM
Creation Date | 8/8/2010 5:23:46 PM

## Extended Properties

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

## Permissions

No object level permissions defined.

## Parameters

Parameter Name | Datatype | Has Default Value | Default Value | Read Only | Xml Document | Description
--- | --- | --- | --- | --- | --- | ---
@PersonID | int |  |  |  |  | Input parameter for the table value function ufnGetContactInformation. Enter a valid PersonID from the Person.Contact table.

## Recordset Returned

No resulting record sets.

## Objects that depend on [ufnGetContactInformation]

- dbo.ufnGetContactInformation

## Objects on which [ufnGetContactInformation] depends

- dbo.ufnGetContactInformation
  - HumanResources.Employee
    - dbo.Flag
    - Person.Person
      - dbo.Name
      - dbo.NameStyle
      - Demographics.exist
      - Person.AdditionalContactInfoSchemaCollection
      - Person.BusinessEntity
      - Person.IndividualSurveySchemaCollection
  - Person.BusinessEntityContact
    - Person.ContactType
  - Purchasing.Vendor
    - dbo.AccountNumber
    - dbo.uspLogError
      - dbo.ErrorLog
      - dbo.uspPrintError
  - Sales.Customer
    - dbo.ufnLeadingZeros
    - Sales.SalesTerritory
      - Person.CountryRegion
    - Sales.Store
      - Sales.SalesPerson
      - Sales.StoreSurveySchemaCollection

## SQL

Script
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE FUNCTION [dbo].[ufnGetContactInformation](@PersonID int) RETURNS @retContactInformation TABLE ( [PersonID] int NOT NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [JobTitle] [nvarchar](50) NULL, [BusinessEntityType] [nvarchar](50) NULL ) AS 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; EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Input parameter for the table value function ufnGetContactInformation. Enter a valid PersonID from the Person.Contact table.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'ufnGetContactInformation', @level2type=N'PARAMETER',@level2name=N'@PersonID' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Table value function returning the first name, last name, job title and contact type for a given contact. - mobi' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'ufnGetContactInformation'

#### See also

List of Functions