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	08/01/2010 08:41
Encrypted
Ansi Nulls

Parameters

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

Objects that dbo.ufnGetContactInformation depends on

Database Object	Object Type	Description	Dep Level
dbo.AccountNumber	User Defined Data Type		3
Person.Contact	Table	Names of each employee, customer contact, and vendor contact.	1
Person.ContactType	Table	Lookup table containing the types of contacts stored in Contact.	1
Sales.Customer	Table	Current customer information. Also see the Individual and Store tables.	2
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.	4
Sales.Individual	Table	Demographic data about customers that purchase Adventure Works products online.	1
dbo.NameStyle	User Defined Data Type		2
dbo.Phone	User Defined Data Type		2
Sales.SalesPerson	Table	Sales representative current information.	3
Sales.SalesTerritory	Table	Sales territory lookup table.	3
Sales.Store	Table	Customers (resellers) of Adventure Works products.	2
Sales.StoreContact	Table	Cross-reference table mapping stores and their employees.	1
dbo.ufnLeadingZeros	User Defined Function	Scalar function used by the Sales.Customer table to help set the account number.	3
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.	3
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.	3
Purchasing.Vendor	Table	Companies from whom Adventure Works Cycles purchases parts or other goods.	2
Purchasing.VendorContact	Table	Cross-reference table mapping vendors and their employees.	1

Procedure Source Code

CREATE FUNCTION [dbo].[ufnGetContactInformation](@ContactID int)
RETURNS @retContactInformation TABLE 
(
[ContactID] int PRIMARY KEY NOT NULL, 
[FirstName] [nvarchar](50) NULL, 
[LastName] [nvarchar](50) NULL, 
[JobTitle] [nvarchar](50) NULL, 
[ContactType] [nvarchar](50) NULL
)
AS 

BEGIN
DECLARE 
@FirstName [nvarchar](50), 
@LastName [nvarchar](50), 
@JobTitle [nvarchar](50), 
@ContactType [nvarchar](50);
SELECT 
@ContactID = ContactID, 
@FirstName = FirstName, 
@LastName = LastName
FROM [Person].[Contact] 
WHERE [ContactID] = @ContactID;
SET @JobTitle = 
CASE 
WHEN EXISTS(SELECT * FROM [HumanResources].[Employee] e 
WHERE e.[ContactID] = @ContactID) 
THEN (SELECT [Title] 
FROM [HumanResources].[Employee] 
WHERE [ContactID] = @ContactID)
WHEN EXISTS(SELECT * FROM [Purchasing].[VendorContact] vc 
INNER JOIN [Person].[ContactType] ct 
ON vc.[ContactTypeID] = ct.[ContactTypeID] 
WHERE vc.[ContactID] = @ContactID) 
THEN (SELECT ct.[Name] 
FROM [Purchasing].[VendorContact] vc 
INNER JOIN [Person].[ContactType] ct 
ON vc.[ContactTypeID] = ct.[ContactTypeID] 
WHERE vc.[ContactID] = @ContactID)
WHEN EXISTS(SELECT * FROM [Sales].[StoreContact] sc 
INNER JOIN [Person].[ContactType] ct 
ON sc.[ContactTypeID] = ct.[ContactTypeID] 
WHERE sc.[ContactID] = @ContactID) 
THEN (SELECT ct.[Name] 
FROM [Sales].[StoreContact] sc 
INNER JOIN [Person].[ContactType] ct 
ON sc.[ContactTypeID] = ct.[ContactTypeID] 
WHERE [ContactID] = @ContactID)
ELSE NULL 
END;
SET @ContactType = 
CASE 
WHEN EXISTS(SELECT * FROM [HumanResources].[Employee] e 
WHERE e.[ContactID] = @ContactID) 
THEN 'Employee'
WHEN EXISTS(SELECT * FROM [Purchasing].[VendorContact] vc 
INNER JOIN [Person].[ContactType] ct 
ON vc.[ContactTypeID] = ct.[ContactTypeID] 
WHERE vc.[ContactID] = @ContactID) 
THEN 'Vendor Contact'
WHEN EXISTS(SELECT * FROM [Sales].[StoreContact] sc 
INNER JOIN [Person].[ContactType] ct 
ON sc.[ContactTypeID] = ct.[ContactTypeID] 
WHERE sc.[ContactID] = @ContactID) 
THEN 'Store Contact'
WHEN EXISTS(SELECT * FROM [Sales].[Individual] i 
WHERE i.[ContactID] = @ContactID) 
THEN 'Consumer'
END;
IF @ContactID IS NOT NULL 
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;

Related Objects

dbo Schema
AdventureWorks Database