Partilhar via

  • Artigo

Funções definidas pelo usuário com valor de tabela

Funções definidas pelo usuário que retornam um tipo de dados table podem ser alternativas excelentes às exibições. Essas funções são referidas como funções com valor de tabela. Uma função definida pelo usuário com valor de tabela pode ser usada onde são permitidas expressões de tabela ou de exibição em consultas Transact-SQL. Enquanto as exibições são limitadas a uma única instrução SELECT, as funções definidas pelo usuário podem conter instruções adicionais, que permitem uma lógica mais poderosa do que é possível nas exibição.

Uma função definida pelo usuário com valor de tabela também pode substituir procedimentos armazenados que retornam um único conjunto de resultados. A tabela retornada por uma função definida pelo usuário pode ser referenciada na cláusula FROM de uma instrução Transact-SQL, mas os procedimentos armazenados que retornam conjuntos de resultados não podem.

Componentes de uma função definida pelo usuário com valor de tabela

Em uma função definida pelo usuário com valor de tabela:

  • A cláusula RETURNS define um nome local de variável de retorno para a tabela retornada pela função. A cláusula RETURNS também define o formato da tabela. O escopo do nome local da variável de retorno é local dentro da função.

  • As instruções Transact-SQL no corpo da função criam e inserem linhas na variável retornada definida pela cláusula RETURNS.

  • As instruções no corpo da função criam e inserem linhas na variável retornada definida pela cláusula RETURNS. A instrução RETURN não pode ter um argumento.

Nenhuma instrução Transact-SQL em uma função com valor de tabela pode retornar um conjunto de resultados diretamente a um usuário. As únicas informações que a função pode retornar ao usuário é a table retornada pela função.

ObservaçãoObservação

A opção de tabela text in row é configurada automaticamente para 256, para uma tabela retornada por uma função definida pelo usuário. Isso não pode ser alterado. As instruções READTEXT, WRITETEXT, e UPDATETEXT não podem ser usadas para ler ou gravar partes de nenhum text, ntext ou colunas na tabela image. Para obter mais informações, consulte Dados em linha.

Exemplo

O seguinte exemplo cria a função dbo.ufnGetContactInformation e demonstra os componentes da função com valor de tabela. Nessa função, o nome da variável de retorno local é @retContactInformation. As instruções no corpo da função inserem linhas nessa variável para construir o resultado de tabela retornado pela função.

USE AdventureWorks;
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 = ContactID, 
        @FirstName = FirstName, 
        @LastName = LastName
    FROM Person.Contact 
    WHERE ContactID = @ContactID;
    SELECT @JobTitle = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e 
                WHERE e.ContactID = @ContactID) 
                THEN (SELECT Title 
                    FROM HumanResources.Employee 
                    WHERE ContactID = @ContactID)
            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Purchasing.VendorContact AS vc 
                    INNER JOIN Person.ContactType AS ct 
                    ON vc.ContactTypeID = ct.ContactTypeID 
                WHERE vc.ContactID = @ContactID) 
                THEN (SELECT ct.Name 
                    FROM Purchasing.VendorContact AS vc 
                        INNER JOIN Person.ContactType AS ct 
                        ON vc.ContactTypeID = ct.ContactTypeID 
                    WHERE vc.ContactID = @ContactID)
            -- Check for store
            WHEN EXISTS(SELECT * FROM Sales.StoreContact AS sc 
                    INNER JOIN Person.ContactType AS ct 
                    ON sc.ContactTypeID = ct.ContactTypeID 
                WHERE sc.ContactID = @ContactID) 
                THEN (SELECT ct.Name 
                    FROM Sales.StoreContact AS sc 
                        INNER JOIN Person.ContactType AS ct 
                        ON sc.ContactTypeID = ct.ContactTypeID 
                    WHERE ContactID = @ContactID)
            ELSE NULL 
        END;
    SET @ContactType = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e 
                WHERE e.ContactID = @ContactID) 
                THEN 'Employee'
            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Purchasing.VendorContact AS vc 
                    INNER JOIN Person.ContactType AS ct 
                    ON vc.ContactTypeID = ct.ContactTypeID 
                WHERE vc.ContactID = @ContactID) 
                THEN 'Vendor Contact'
            -- Check for store
            WHEN EXISTS(SELECT * FROM Sales.StoreContact AS sc 
                    INNER JOIN Person.ContactType AS ct 
                    ON sc.ContactTypeID = ct.ContactTypeID 
                WHERE sc.ContactID = @ContactID) 
                THEN 'Store Contact'
            -- Check for individual consumer
            WHEN EXISTS(SELECT * FROM Sales.Individual AS 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;
GO

O seguinte exemplo usa a função com valor de tabela dbo.ufnGetContactInformation na cláusula FROM das duas instruções SELECT.

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