Udostępnij za pośrednictwem


Funkcje oródwierszową zdefiniowaną przez użytkownika

Zdefiniowane przez użytkownika funkcje zwracające table Typ danych może być wydajne rozwiązania alternatywne do widoków.Funkcje te są zwane tabela-wycenione funkcji.Oródwierszową zdefiniowaną przez użytkownika funkcja mogą być używane, gdy wyrażenia tabela lub widoku są dozwolone w Transact-SQL kwerend.Widoki są ograniczone do pojedynczej instrukcja SELECT, funkcje zdefiniowane przez użytkownika mogą zawierać dodatkowe instrukcje, które umożliwiają logiki bardziej wydajne niż jest to możliwe w widokach.

A tabela-ważnych funkcji zdefiniowanej przez użytkownika można także zastąpić procedur przechowywanych, które zwracają zestaw pojedynczy wynik.Tabela zwrócona przez funkcja zdefiniowaną przez użytkownika można odwoływać się w klauzula FROM Transact-SQL instrukcja, ale procedur przechowywanych, które zwracają zestaw wyników nie.

Składniki funkcją oródwierszową zdefiniowaną przez użytkownika

W tabela-wycenione funkcja zdefiniowana przez użytkownika:

  • Klauzula zwraca definiuje zwrotu lokalne nazwy zmiennej tabela zwrócone przez funkcja.Klauzula zwraca również definiuje format tabela.Zakres lokalny zwrotu nazwa zmiennej jest lokalny wewnątrz funkcja.

  • Transact-SQL Instrukcji w treści funkcja tworzenia i wstawia wiersze do zwrotu zmiennej zdefiniowane przez zwraca klauzula.

  • Podczas wykonywania instrukcja RETURN wiersze wstawione do zmiennej są zwracane jako produkcja tabelarycznej funkcja.Instrukcja RETURN nie może mieć argument.

Nie Transact-SQL instrukcji w funkcja zwracająca tabelę można zwrócić zestaw wyników bezpośrednio do użytkownika.Jedyną informacją, funkcja może zwrócić się do użytkownika jest table zwrócone przez funkcję.

Ostrzeżenie

text in row Opcja tabela jest automatycznie zestaw do 256 tabela zwrócone przez funkcja zdefiniowaną przez użytkownika.Nie można zmienić.Nie można użyć instrukcji READTEXT, WRITETEXT i UPDATETEXT do odczytu lub zapisu dowolnej części text, ntext, lub image kolumn w tabela.Aby uzyskać więcej informacji, zobacz W wierszu danych.

Przykład

Poniższy przykład tworzy dbo.ufnGetContactInformation funkcja i demonstruje składników funkcja zwracająca tabelę.Funkcja ta jest lokalna nazwa zmiennej zwrotu @retContactInformation.Instrukcje w treści funkcja Wstaw wiersze do tej zmiennej do budowania wynik tabela zwrócone przez funkcja.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufnGetContactInformation;
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 = BusinessEntityID, 
        @FirstName = FirstName, 
        @LastName = LastName
    FROM Person.Person 
    WHERE BusinessEntityID = @ContactID;
    -- Get contact job title
    SELECT @JobTitle = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'EM') 
                THEN (SELECT JobTitle 
                      FROM HumanResources.Employee AS e
                      WHERE e.BusinessEntityID = @ContactID)
            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'VC') 
                THEN (SELECT ct.Name 
                      FROM Person.ContactType AS ct 
                      INNER JOIN Person.BusinessEntityContact AS bec 
                          ON bec.ContactTypeID = ct.ContactTypeID  
                      WHERE bec.PersonID = @ContactID)

            -- Check for store
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'SC') 
                THEN (SELECT ct.Name 
                      FROM Person.ContactType AS ct 
                      INNER JOIN Person.BusinessEntityContact AS bec 
                          ON bec.ContactTypeID = ct.ContactTypeID  
                      WHERE bec.PersonID = @ContactID)
            ELSE NULL 
        END;
    -- Get contact type
    SET @ContactType = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'EM') 
            THEN 'Employee'
            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'VC')
            THEN 'Vendor Contact'
            -- Check for store
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'SC')
            THEN 'Store Contact'
            -- Check for individual consumer
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'IN') 
            THEN 'Consumer'
             -- Check for general contact
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'GC') 
            THEN 'General Contact'
        END;
    -- Return the information to the caller
    IF @ContactID IS NOT NULL 
    BEGIN
        INSERT @retContactInformation
        SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
    END;
    RETURN;
END;
GO

W poniższym przykładzie użyto funkcja zwracająca tabelę dbo.ufnGetContactInformation w klauzula FROM dwóch instrukcji SELECT.

USE AdventureWorks2008R2;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(1209);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO