Fala pessoal!
Nesse post de hoje eu gostaria de demonstrar um recurso disponível desde o SQL Server 2008 e que vejo pouquíssimos casos de uso no dia a dia nas empresas que trabalhei, que é o uso de “tabelas” como parâmetros para Stored Procedures.

Introdução

Se você é um cara que gosta de gambiarras, tenho certeza que pensou que eu estava me referindo a passar o nome da tabela como parâmetro do tipo VARCHAR e usar query dinâmica para ler os dados da tabela, né ? Pois é, mas não é sobre isso o post não.. rs

O objetivo desse artigo, é mostrar como utilizar tipos de tabelas de tabela para conseguir passar como parâmetro de Stored Procedures tipos de dados complexos, simulando uma tabela mesmo do banco de dados.

Utilizar esse tipo de objeto possui uma série de vantagens para a nossa programação:

• Não geram locks
• Sâo armazenadas em cache, assim como tabelas temporárias
• Simplifica a programação
• Traz regras de negócio para a modelagem dos dados
• Fornece um modelo de tipagem forte
• Permite fornecer tabelas e dados estruturados para Stored Procedures
• Pode ser criado utilizando In-Memory OLTP = WITH(MEMORY_OPTIMIZED=ON)

Entretanto, devemos também nos atentar para as restrições:

• SQL Server não mantém estatísticas em colunas de parâmetros com valor de tabela.
• Os parâmetros com valor de tabela devem ser passados como parâmetros de entrada READONLY para rotinas Transact-SQL . Não é possível executar operações DML como UPDATE, DELETE ou INSERT em um parâmetro com valor de tabela no corpo de uma rotina.
• Você não pode usar um parâmetro com valor de tabela como destino de uma instrução SELECT INTO ou INSERT EXEC. Um parâmetro com valor de tabela pode estar na cláusula FROM de SELECT INTO, em uma string ou INSERT EXEC de Stored Procedures.
• Um tipo tabela não pode ser alterado. Caso necessite alterá-lo, você precisará recriá-lo. Caso ele seja utilizado como parâmetros em objetos, o tipo terá que ser desassociado desses objetos, excluído e depois criado novamente com a estrutura desejada
• Você não pode criar índices diretamente no tipo com estrutura de tabela (mais abaixo vou demonstrar como criar índices)

Criando o nosso primeiro tipo com estrutura de tabela

O primeiro passo para isso, é criar o nosso tipo de dados do tipo tabela:

CREATE TYPE dbo.tpPessoa AS TABLE (
Nome varchar(100),
Idade INT,
Dt_Nascimento DATE
)

Agora, vamos criar uma Stored Procedure simples, que vai receber uma @variavel do tipo tpPessoa, que acabamos de criar e vai simplesmente mostrar na tela esses dados:

CREATE PROCEDURE dbo.stpExibe_Pessoa (
    @Pessoa tpPessoa READONLY
)
AS
BEGIN

    SELECT *
    FROM @Pessoa

END
GO

Após criar essa Stored Procedure, vamos declarar uma @variável utilizando o tipo tpPessoa, inserir alguns valores e depois, executar a Stored Procedure stpExibe_Pessoa, informando a @variável como parâmetro:

DECLARE @Variavel_Pessoa AS tpPessoa

INSERT INTO @Variavel_Pessoa
(
    Nome,
    Idade,
    Dt_Nascimento
)
VALUES
( 'Dirceu Resende', 31, '1987-05-28'),
( 'Patrícia', 31, '1987-01-15'),
( 'Letícia', 21, '1997-04-15')


EXEC dbo.stpExibe_Pessoa 
    @Pessoa = @Variavel_Pessoa -- tpPessoa

 

RESULTADO:

E agora, visualizando o código todo de uma vez:

-- Cria o tipo tpPessoa
CREATE TYPE dbo.tpPessoa AS TABLE (
    Nome varchar(100),
    Idade INT,
    Dt_Nascimento DATE
)
GO

-- Cria uma procedure que você irá receber como parâmetro uma variável do tipo tpPessoa
CREATE PROCEDURE dbo.stpExibe_Pessoa (
    @Pessoa tpPessoa READONLY
)
AS
BEGIN

    SELECT *
    FROM @Pessoa

END
GO


-- Instancia uma variável @Variavel_Pessoa, do tipo tpPessoa, popula os dados e executa a SP stpExibe_Pessoa
DECLARE @Variavel_Pessoa AS tpPessoa

INSERT INTO @Variavel_Pessoa
(
    Nome,
    Idade,
    Dt_Nascimento
)
VALUES
( 'Dirceu Resende', 31, '1987-05-28'),
( 'Patrícia', 31, '1987-01-15'),
( 'Letícia', 21, '1997-04-15')


EXEC dbo.stpExibe_Pessoa 
    @Pessoa = @Variavel_Pessoa -- tpPessoa

É importante observar que, após a criação do tipo e utilização do mesmo em uma Stored Procedure, por exemplo, você só conseguirá excluir o tipo se ele for desassociado da SP. Caso contrário, irá receber essa mensagem de erro ao tentar exclui-lo (lembrando que não é possível alterar o tipo com estrutura de tabela):

Msg 3732, Level 16, State 1, Line 2
Cannot drop type ‘dbo.tpPessoa’ because it is being referenced by object ‘stpExibe_Pessoa’. There may be other objects that reference this type.

Tipos com estruturas de tabelas mais complexas

Agora, vamos tentar um exemplo um pouco mais complexo e com algumas regras de negócio:

IF (OBJECT_ID('dbo.stpExibe_Pessoa') IS NOT NULL) DROP PROCEDURE dbo.stpExibe_Pessoa
IF (EXISTS(SELECT NULL FROM sys.types WHERE [name] = 'tpPessoa')) DROP TYPE dbo.tpPessoa

-- Cria o tipo tpPessoa
CREATE TYPE dbo.tpPessoa AS TABLE (
    Id INT IDENTITY(1,1) NOT NULL,
    Dt_Registro DATETIME NOT NULL DEFAULT GETDATE(), -- Constraint de Check Default
    Nome VARCHAR(100) NOT NULL PRIMARY KEY CLUSTERED, -- PRIMARY KEY e ÍNDICE CLUSTERED :)
    Dt_Nascimento DATE NULL,
    Nr_Documento VARCHAR(11) NOT NULL UNIQUE, -- índice UNIQUE :)
    Idade AS (DATEDIFF(YEAR, Dt_Nascimento, GETDATE())) -- Coluna calculada
)
GO


-- Cria uma procedure que você irá receber como parâmetro uma variável do tipo tpPessoa
CREATE PROCEDURE dbo.stpExibe_Pessoa (
    @Pessoa tpPessoa READONLY
)
AS
BEGIN

    SELECT *
    FROM @Pessoa

END
GO


-- Cria uma tabela com alguns dados de teste
IF (OBJECT_ID('tempdb..#Teste') IS NOT NULL) DROP TABLE #Teste
CREATE TABLE #Teste (
    Nome VARCHAR(100),
    Dt_Nascimento DATETIME,
    Nr_Documento VARCHAR(20)
)

INSERT INTO #Teste
(
    Nome,
    Dt_Nascimento,
    Nr_Documento
)
VALUES
( 'Dirceu Resende', '1987-05-28', '1111111'),
( 'Patrícia', '1987-01-15', '2222222'),
( 'Letícia', '1997-04-15', '33333333'),
( 'Erro no UNIQUE', '2018-09-18', '1111111')


-- Instancia uma variável @Variavel_Pessoa, do tipo tpPessoa e popula os dados com a tabela #Teste
DECLARE @Variavel_Pessoa AS tpPessoa

INSERT INTO @Variavel_Pessoa
(
    Nome,
    Dt_Nascimento,
    Nr_Documento
)
SELECT 
    Nome,
    Dt_Nascimento,
    Nr_Documento
FROM 
    #Teste


-- Executa SP stpExibe_Pessoa passando com parâmetro a variável @Variavel_Pessoa do tipo tpPessoa
EXEC dbo.stpExibe_Pessoa 
    @Pessoa = @Variavel_Pessoa -- tpPessoa

Ao tentar executar esse código acima, vamos nos deparar com a mensagem de erro abaixo, o que comprova que o índice UNIQUE que criamos no tipo está realmente funcionando:

Msg 2627, Level 14, State 1, Line 55
Violation of UNIQUE KEY constraint ‘UQ__#A8CD763__C7F1EF4BE5A1C81E’. Cannot insert duplicate key in object ‘dbo.@Variavel_Pessoa’. The duplicate key value is (1111111).
The statement has been terminated.

Após corrigir os dados de entrada (substituí o documento “1111111” por “4444444”) e executar novamente o script, vemos que ele nos retorna esse conjunto de dados:

Comparando com o resultado anterior, vemos que desta vez ele está ordenando os resultados pelo Nome, uma vez que declaramos um índice clustered no campo Nome. Embora o índice clustered não garanta a ordenação dos registros, vimos que ele está sendo colocado em prática no tipo tpPessoa.

Ainda não está convencido que o índice realmente existe e está sendo utilizado no tipo tpPessoa ? Ok!

E se a gente tentar criar um índice depois que o tipo tpPessoa foi criado?

CREATE NONCLUSTERED INDEX SK01_tpPessoa ON dbo.tpPessoa(Nr_Documento)

Resultado:

Msg 1088, Level 16, State 12, Line 15
Cannot find the object “dbo.tpPessoa” because it does not exist or you do not have permissions.

E o In-Memory OLTP? Funciona? Sim!!

CREATE TYPE dbo.tpPessoa AS TABLE (
    Nome VARCHAR(100) NOT NULL,
    Nr_Documento VARCHAR(11) NOT NULL,
    Idade AS (DATEDIFF(YEAR, Dt_Nascimento, GETDATE())) -- Coluna calculada
) WITH(MEMORY_OPTIMIZED=ON)
GO

Uso de tipos com estrutura de tabela em funções

Até então, demonstrei como utilizar a tpPessoa em Stored Procedures. Mas será que é possível utilizá-la em funções ?

Vamos testar:

IF (OBJECT_ID('dbo.fncRetorna_Mais_Velho') IS NOT NULL) DROP FUNCTION dbo.fncRetorna_Mais_Velho
IF (EXISTS(SELECT NULL FROM sys.types WHERE [name] = 'tpPessoa')) DROP TYPE dbo.tpPessoa

-- Cria o tipo tpPessoa
CREATE TYPE dbo.tpPessoa AS TABLE (
    Nome VARCHAR(100) NOT NULL, -- PRIMARY KEY e ÍNDICE CLUSTERED :)
    Dt_Nascimento DATETIME NOT NULL,
    Idade AS (DATEDIFF(YEAR, Dt_Nascimento, GETDATE())) -- Coluna calculada
)
GO


-- Cria uma procedure que você irá receber como parâmetro uma variável do tipo tpPessoa
CREATE FUNCTION dbo.fncRetorna_Mais_Velho (
    @Pessoa tpPessoa READONLY
)
RETURNS VARCHAR(100)
AS
BEGIN
    
    RETURN (SELECT TOP(1) Nome FROM @Pessoa ORDER BY Idade DESC)

END
GO


-- Cria uma tabela com alguns dados de teste
IF (OBJECT_ID('tempdb..#Teste') IS NOT NULL) DROP TABLE #Teste
CREATE TABLE #Teste (
    Nome VARCHAR(100),
    Dt_Nascimento DATETIME
)

INSERT INTO #Teste
(
    Nome,
    Dt_Nascimento
)
VALUES
( 'Dirceu Resende', '1987-05-28'),
( 'Patrícia', '1987-01-15'),
( 'Letícia', '1997-04-15'),
( 'Erro no UNIQUE', '2018-09-18')


-- Instancia uma variável @Variavel_Pessoa, do tipo tpPessoa e popula os dados com a tabela #Teste
DECLARE @Variavel_Pessoa AS tpPessoa

INSERT INTO @Variavel_Pessoa
SELECT Nome, Dt_Nascimento
FROM #Teste

-- Executa SP stpExibe_Pessoa passando com parâmetro a variável @Variavel_Pessoa do tipo tpPessoa
SELECT dbo.fncRetorna_Mais_Velho(@Variavel_Pessoa)

E se a gente tentar em funções do tipo table-valued ?

IF (OBJECT_ID('dbo.fncRetorna_Datas_Pessoa') IS NOT NULL) DROP FUNCTION dbo.fncRetorna_Datas_Pessoa
IF (EXISTS(SELECT NULL FROM sys.types WHERE [name] = 'tpPessoa')) DROP TYPE dbo.tpPessoa

-- Cria o tipo tpPessoa
CREATE TYPE dbo.tpPessoa AS TABLE (
    Nome VARCHAR(100) NOT NULL, -- PRIMARY KEY e ÍNDICE CLUSTERED :)
    Dt_Nascimento DATETIME NOT NULL,
    Idade AS (DATEDIFF(YEAR, Dt_Nascimento, GETDATE())) -- Coluna calculada
)
GO


-- Cria uma procedure que você irá receber como parâmetro uma variável do tipo tpPessoa
CREATE FUNCTION dbo.fncRetorna_Datas_Pessoa (
    @Pessoa tpPessoa READONLY
)
RETURNS TABLE
AS
    
    RETURN 
        SELECT Nome, YEAR(Dt_Nascimento) AS Ano_Nascimento, MONTH(Dt_Nascimento) AS Mes_Nascimento, DAY(Dt_Nascimento) AS Dia_Nascimento
        FROM @Pessoa

GO


-- Cria uma tabela com alguns dados de teste
IF (OBJECT_ID('tempdb..#Teste') IS NOT NULL) DROP TABLE #Teste
CREATE TABLE #Teste (
    Nome VARCHAR(100),
    Dt_Nascimento DATETIME
)

INSERT INTO #Teste
(
    Nome,
    Dt_Nascimento
)
VALUES
( 'Dirceu Resende', '1987-05-28'),
( 'Patrícia', '1987-01-15'),
( 'Letícia', '1997-04-15'),
( 'Erro no UNIQUE', '2018-09-18')


-- Instancia uma variável @Variavel_Pessoa, do tipo tpPessoa e popula os dados com a tabela #Teste
DECLARE @Variavel_Pessoa AS tpPessoa

INSERT INTO @Variavel_Pessoa
SELECT Nome, Dt_Nascimento
FROM #Teste


-- Executa SP stpExibe_Pessoa passando com parâmetro a variável @Variavel_Pessoa do tipo tpPessoa
SELECT * FROM dbo.fncRetorna_Datas_Pessoa(@Variavel_Pessoa) RESULTADO: (funcionou!)

É isso aí, pessoal!
Espero que tenham gostado desse post e até a próxima!

Referências:
https://docs.microsoft.com/pt-br/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017
https://docs.microsoft.com/pt-br/sql/t-sql/statements/create-type-transact-sql?view=sql-server-2017

Artigo original em https://www.dirceuresende.com/blog/sql-server-como-passar-uma-tabela-como-parametro-para-stored-procedures-e-funcoes/