• 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 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

O seguinte exemplo usa a função com valor de tabela dbo.ufnGetContactInformation na cláusula FROM das duas instruções 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