經典的多語句表值函數

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;

本文轉自yonghu86 51CTO部落格，原文連結：http://blog.51cto.com/yonghu/1321453，如需轉載請自行聯系原作者