經典的多語句表值函數

版權聲明：本文為部落客原創文章，未經部落客允許不得轉載。 https://blog.csdn.net/chinahuyong/article/details/3092558

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

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

                    ON sc.[ContactTypeID] = ct.[ContactTypeID]

                WHERE sc.[ContactID] = @ContactID)

                    FROM [Sales].[StoreContact] sc

                        ON sc.[ContactTypeID] = ct.[ContactTypeID]

            ELSE NULL

        END;

    SET @ContactType =

                THEN 'Employee'

                THEN 'Vendor Contact'

                THEN 'Store Contact'

            -- Check for individual consumer

            WHEN EXISTS(SELECT * FROM [Sales].[Individual] i

                WHERE i.[ContactID] = @ContactID)

                THEN 'Consumer'

    -- Return the information to the caller

    IF @ContactID IS NOT NULL

    BEGIN

        INSERT @retContactInformation

        SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;

    END;

    RETURN;

END;