Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server – Como passar uma tabela como parâmetro para Stored Procedures e Funções

Visualizações: 8.040 views
Tempo de Leitura: 7 minutos

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

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:

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:

Resultado:

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

É 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:

Visualizar código-fonte

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?

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

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:
Visualizar código

Resultado: (funcionou!)

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

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