Dbo.ufnGetContactInformation (function) - Revision history

Bot at 22:15, 23 June 2010

2010-06-23T22:15:28Z

Bot at 21:59, 23 June 2010

2010-06-23T21:59:39Z

Bot at 22:09, 2 February 2010

2010-02-02T22:09:06Z

Bot at 21:56, 28 December 2009

2009-12-28T21:56:26Z

Bot at 06:01, 30 November 2009

2009-11-30T06:01:55Z

Bot at 05:40, 30 November 2009

2009-11-30T05:40:08Z

Bot at 05:26, 30 November 2009

2009-11-30T05:26:34Z

Bot at 22:59, 26 November 2009

2009-11-26T22:59:49Z

Bot: New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''function | dbo.ufnGetContactInformation |- |}

 CREATE FUNCTION [dbo].[ufnGetCont...

2009-01-03T12:35:58Z

New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''function | dbo.ufnGetContactInformation |- |} <pre> CREATE FUNCTION [dbo].[ufnGetCont...

New page

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|-
| '''function
| dbo.ufnGetContactInformation
|-
|}

<pre>
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;
</pre>

@@ Line 121: / Line 121: @@
 | Select
 | Table
-| [[HumanResources.Employee_(_(table))|HumanResources.Employee]]
+| [[HumanResources.Employee_(table)|HumanResources.Employee]]
 |- valign="top"
 | Select
 | Table
-| [[Person.Contact_(_(table))|Person.Contact]]
+| [[Person.Contact_(table)|Person.Contact]]
 |- valign="top"
 | Select
 | Table
-| [[Person.ContactType_(_(table))|Person.ContactType]]
+| [[Person.ContactType_(table)|Person.ContactType]]
 |- valign="top"
 | Select
 | Table
-| [[Purchasing.VendorContact_(_(table))|Purchasing.VendorContact]]
+| [[Purchasing.VendorContact_(table)|Purchasing.VendorContact]]
 |- valign="top"
 | Select
 | Table
-| [[Sales.Individual_(_(table))|Sales.Individual]]
+| [[Sales.Individual_(table)|Sales.Individual]]
 |- valign="top"
 | Select
 | Table
-| [[Sales.StoreContact_(_(table))|Sales.StoreContact]]
+| [[Sales.StoreContact_(table)|Sales.StoreContact]]
 |}

@@ Line 2: / Line 2: @@
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
-|-
+|- valign="top"
 | '''Function
 | dbo.ufnGetContactInformation
@@ Line 8: / Line 8: @@
 | '''Description
 | Table value function returning the first name, last name, job title and contact type for a given contact.
 |}
 === Source ===
@@ Line 120: / Line 118: @@
 | '''Referenced Object
-|-
+|- valign="top"
 | Select
 | Table
-| [[HumanResources.Employee_(table)|HumanResources.Employee]]
+| [[HumanResources.Employee_(_(table))|HumanResources.Employee]]
-|-
+|- valign="top"
 | Select
 | Table
-| [[Person.Contact_(table)|Person.Contact]]
+| [[Person.Contact_(_(table))|Person.Contact]]
-|-
+|- valign="top"
 | Select
 | Table
-| [[Person.ContactType_(table)|Person.ContactType]]
+| [[Person.ContactType_(_(table))|Person.ContactType]]
-|-
+|- valign="top"
 | Select
 | Table
-| [[Purchasing.VendorContact_(table)|Purchasing.VendorContact]]
+| [[Purchasing.VendorContact_(_(table))|Purchasing.VendorContact]]
-|-
+|- valign="top"
 | Select
 | Table
-| [[Sales.Individual_(table)|Sales.Individual]]
+| [[Sales.Individual_(_(table))|Sales.Individual]]
-|-
+|- valign="top"
 | Select
 | Table
-| [[Sales.StoreContact_(table)|Sales.StoreContact]]
+| [[Sales.StoreContact_(_(table))|Sales.StoreContact]]
 |}

@@ Line 3: / Line 3: @@
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 |-
-| '''function
+| '''Function
 | dbo.ufnGetContactInformation
 |- valign="top"
@@ Line 11: / Line 11: @@
 |}
 <pre>
 CREATE FUNCTION [dbo].[ufnGetContactInformation](@ContactID int)
@@ Line 111: / Line 113: @@
 </pre>
+=== References ===
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 |- style="background:silver"
@@ Line 149: / Line 151: @@
 |}

@@ Line 5: / Line 5: @@
 | '''function
 | dbo.ufnGetContactInformation
 |-
 |}
@@ Line 107: / Line 110: @@
 END;
 </pre>
 == automatically generated ==

@@ Line 114: / Line 114: @@
 | dbo.ufnGetContactInformation
 |- valign="top"
 | '''Description
 | Table value function returning the first name, last name, job title and contact type for a given contact.
 |-
 |}

← Older revision		Revision as of 22:59, 26 November 2009
Line 107:		Line 107:
	END;		END;
	</pre>		</pre>
		+
		+	== automatically generated ==
		+
		+	{\| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
		+	\|-
		+	\| '''function
		+	\| dbo.ufnGetContactInformation
		+	\|-
		+
		+	\| '''Description
		+	\| Table value function returning the first name, last name, job title and contact type for a given contact.
		+	\|-
		+
		+	\|}
		+
		+	<pre>
		+
		+	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;
		+	</pre>
		+
		+
		+	{\| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
		+	\|- style="background:silver"
		+	\| '''Referenced Object
		+	\| '''Object Type
		+	\| '''Dependency Type
		+
		+	\|-
		+	\| [[HumanResources.Employee_(table)\|HumanResources.Employee]]
		+	\| Table
		+	\| Select
		+	\|-
		+	\| [[Person.Contact_(table)\|Person.Contact]]
		+	\| Table
		+	\| Select
		+	\|-
		+	\| [[Person.ContactType_(table)\|Person.ContactType]]
		+	\| Table
		+	\| Select
		+	\|-
		+	\| [[Purchasing.VendorContact_(table)\|Purchasing.VendorContact]]
		+	\| Table
		+	\| Select
		+	\|-
		+	\| [[Sales.Individual_(table)\|Sales.Individual]]
		+	\| Table
		+	\| Select
		+	\|-
		+	\| [[Sales.StoreContact_(table)\|Sales.StoreContact]]
		+	\| Table
		+	\| Select
		+	\|}