Difference between revisions of "Dbo.ufnGetContactInformation (function)"
Line 5: | Line 5: | ||
| '''function | | '''function | ||
| dbo.ufnGetContactInformation | | dbo.ufnGetContactInformation | ||
+ | |- valign="top" | ||
+ | | '''Description | ||
+ | | Table value function returning the first name, last name, job title and contact type for a given contact. | ||
|- | |- | ||
|} | |} | ||
Line 107: | Line 110: | ||
END; | END; | ||
</pre> | </pre> | ||
+ | |||
+ | |||
+ | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
+ | |- style="background:silver" | ||
+ | | '''Dependency Type | ||
+ | | '''Object Type | ||
+ | | '''Referenced Object | ||
+ | |||
+ | |- | ||
+ | | Select | ||
+ | | Table | ||
+ | | [[HumanResources.Employee_(table)|HumanResources.Employee]] | ||
+ | |||
+ | |- | ||
+ | | Select | ||
+ | | Table | ||
+ | | [[Person.Contact_(table)|Person.Contact]] | ||
+ | |||
+ | |- | ||
+ | | Select | ||
+ | | Table | ||
+ | | [[Person.ContactType_(table)|Person.ContactType]] | ||
+ | |||
+ | |- | ||
+ | | Select | ||
+ | | Table | ||
+ | | [[Purchasing.VendorContact_(table)|Purchasing.VendorContact]] | ||
+ | |||
+ | |- | ||
+ | | Select | ||
+ | | Table | ||
+ | | [[Sales.Individual_(table)|Sales.Individual]] | ||
+ | |||
+ | |- | ||
+ | | Select | ||
+ | | Table | ||
+ | | [[Sales.StoreContact_(table)|Sales.StoreContact]] | ||
+ | |||
+ | |} | ||
+ | |||
+ | |||
+ | |||
== automatically generated == | == automatically generated == | ||
Revision as of 22:56, 28 December 2009
wikibot
function | dbo.ufnGetContactInformation |
Description | Table value function returning the first name, last name, job title and contact type for a given contact. |
CREATE FUNCTION [dbo].[ufnGetContactInformation](@ContactID int) RETURNS @retContactInformation TABLE ( -- Columns returned by the function [ContactID] int PRIMARY KEY NOT NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [JobTitle] [nvarchar](50) NULL, [ContactType] [nvarchar](50) NULL ) AS -- Returns the first name, last name, job title and contact type for the specified contact. BEGIN DECLARE @FirstName [nvarchar](50), @LastName [nvarchar](50), @JobTitle [nvarchar](50), @ContactType [nvarchar](50); -- Get common contact information SELECT @ContactID = ContactID, @FirstName = FirstName, @LastName = LastName FROM [Person].[Contact] WHERE [ContactID] = @ContactID; SET @JobTitle = CASE -- Check for employee WHEN EXISTS(SELECT * FROM [HumanResources].[Employee] e WHERE e.[ContactID] = @ContactID) THEN (SELECT [Title] FROM [HumanResources].[Employee] WHERE [ContactID] = @ContactID) -- Check for vendor 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) -- Check for store 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 -- Check for employee WHEN EXISTS(SELECT * FROM [HumanResources].[Employee] e WHERE e.[ContactID] = @ContactID) THEN 'Employee' -- Check for vendor WHEN EXISTS(SELECT * FROM [Purchasing].[VendorContact] vc INNER JOIN [Person].[ContactType] ct ON vc.[ContactTypeID] = ct.[ContactTypeID] WHERE vc.[ContactID] = @ContactID) THEN 'Vendor Contact' -- Check for store WHEN EXISTS(SELECT * FROM [Sales].[StoreContact] sc INNER JOIN [Person].[ContactType] ct ON sc.[ContactTypeID] = ct.[ContactTypeID] WHERE sc.[ContactID] = @ContactID) THEN 'Store Contact' -- Check for individual consumer WHEN EXISTS(SELECT * FROM [Sales].[Individual] i WHERE i.[ContactID] = @ContactID) THEN 'Consumer' END; -- Return the information to the caller IF @ContactID IS NOT NULL BEGIN INSERT @retContactInformation SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType; END; RETURN; END;
Dependency Type | Object Type | Referenced Object |
Select | Table | HumanResources.Employee |
Select | Table | Person.Contact |
Select | Table | Person.ContactType |
Select | Table | Purchasing.VendorContact |
Select | Table | Sales.Individual |
Select | Table | Sales.StoreContact |
automatically generated
function | dbo.ufnGetContactInformation |
Description | Table value function returning the first name, last name, job title and contact type for a given contact. |
CREATE FUNCTION [dbo].[ufnGetContactInformation](@ContactID int) RETURNS @retContactInformation TABLE ( -- Columns returned by the function [ContactID] int PRIMARY KEY NOT NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [JobTitle] [nvarchar](50) NULL, [ContactType] [nvarchar](50) NULL ) AS -- Returns the first name, last name, job title and contact type for the specified contact. BEGIN DECLARE @FirstName [nvarchar](50), @LastName [nvarchar](50), @JobTitle [nvarchar](50), @ContactType [nvarchar](50); -- Get common contact information SELECT @ContactID = ContactID, @FirstName = FirstName, @LastName = LastName FROM [Person].[Contact] WHERE [ContactID] = @ContactID; SET @JobTitle = CASE -- Check for employee WHEN EXISTS(SELECT * FROM [HumanResources].[Employee] e WHERE e.[ContactID] = @ContactID) THEN (SELECT [Title] FROM [HumanResources].[Employee] WHERE [ContactID] = @ContactID) -- Check for vendor 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) -- Check for store 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 -- Check for employee WHEN EXISTS(SELECT * FROM [HumanResources].[Employee] e WHERE e.[ContactID] = @ContactID) THEN 'Employee' -- Check for vendor WHEN EXISTS(SELECT * FROM [Purchasing].[VendorContact] vc INNER JOIN [Person].[ContactType] ct ON vc.[ContactTypeID] = ct.[ContactTypeID] WHERE vc.[ContactID] = @ContactID) THEN 'Vendor Contact' -- Check for store WHEN EXISTS(SELECT * FROM [Sales].[StoreContact] sc INNER JOIN [Person].[ContactType] ct ON sc.[ContactTypeID] = ct.[ContactTypeID] WHERE sc.[ContactID] = @ContactID) THEN 'Store Contact' -- Check for individual consumer WHEN EXISTS(SELECT * FROM [Sales].[Individual] i WHERE i.[ContactID] = @ContactID) THEN 'Consumer' END; -- Return the information to the caller IF @ContactID IS NOT NULL BEGIN INSERT @retContactInformation SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType; END; RETURN; END;
Dependency Type | Object Type | Referenced Object |
Select | Table | HumanResources.Employee |
Select | Table | Person.Contact |
Select | Table | Person.ContactType |
Select | Table | Purchasing.VendorContact |
Select | Table | Sales.Individual |
Select | Table | Sales.StoreContact |