Difference between revisions of "Dbo.ufnGetContactInformation (function)"

From dbscript Online Help
Jump to: navigation, search
(New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''function | dbo.ufnGetContactInformation |- |} <pre> CREATE FUNCTION [dbo].[ufnGetCont...)
 
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
 +
|}

Revision as of 23:59, 26 November 2009

wikibot

function dbo.ufnGetContactInformation 
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;

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;


Referenced Object Object Type Dependency Type
HumanResources.Employee Table Select
Person.Contact Table Select
Person.ContactType Table Select
Purchasing.VendorContact Table Select
Sales.Individual Table Select
Sales.StoreContact Table Select
Retrieved from "http://dbshelp.devio.at/index.php?title=Dbo.ufnGetContactInformation_(function)&oldid=1298"