SQL Server - Cómo pasar una tabla como parámetro a Stored Procedures y Funciones

¡Hola, chicos!
En la publicación de hoy me gustaría demostrar una característica que ha estado disponible desde SQL Server 2008 y que veo muy pocos casos de uso a diario en las empresas para las que he trabajado, que es el uso de “tablas” como parámetros para Procedimientos Almacenados.

Introducción

Si eres un tipo al que le gustan los trucos, estoy seguro de que pensaste que me refería a pasar el nombre de la tabla como un parámetro VARCHAR y usar una consulta dinámica para leer los datos de la tabla, ¿verdad? Sí, pero de eso no se trata esta publicación... jajaja

El objetivo de este artículo es mostrar cómo utilizar tipos de tablas para poder pasar tipos de datos complejos como parámetros de Procedimientos Almacenados, simulando una tabla de la base de datos.

Utilizar este tipo de objetos tiene una serie de ventajas para nuestra programación:

No generar bloqueos
Se almacenan en caché, al igual que las tablas temporales.
Simplifica la programación
Aporta reglas de negocio al modelado de datos
Proporciona un modelo de escritura sólido
Le permite proporcionar tablas y datos estructurados para procedimientos almacenados.
Se puede crear usando OLTP en memoria = CON(MEMORY_OPTIMIZED=ON)

Sin embargo, también debemos prestar atención a las restricciones:

SQL Server no mantiene estadísticas sobre columnas de parámetros con valores de tabla.
Los parámetros con valores de tabla se deben pasar como parámetros de entrada READONLY a las rutinas Transact-SQL. No puede realizar operaciones DML como ACTUALIZAR, ELIMINAR o INSERTAR en un parámetro con valores de tabla en el cuerpo de una rutina.
No puede utilizar un parámetro con valores de tabla como destino de una instrucción SELECT INTO o INSERT EXEC. Un parámetro con valores de tabla puede estar en la cláusula FROM de SELECT INTO, en una cadena o en INSERT EXEC de procedimientos almacenados.
No se puede cambiar un tipo de tabla. Si necesita cambiarlo, deberá recrearlo. Si se utiliza como parámetros en objetos, el tipo deberá disociarse de estos objetos, eliminarse y luego crearse nuevamente con la estructura deseada.
No puede crear índices directamente en el tipo con estructura de tabla (a continuación demostraré cómo crear índices)

Creando nuestro primer tipo con estructura de tabla.

El primer paso para esto es crear nuestro tipo de datos de tabla:

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

Ahora, creemos un Procedimiento Almacenado simple, que recibirá una @variable de tipo tpPessoa, que acabamos de crear y simplemente mostrará estos datos en la pantalla:

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

    SELECT *
    FROM @Pessoa

END
GO

Luego de crear este Procedimiento Almacenado, declararemos una @variable usando el tipo tpPessoa, insertaremos algunos valores y luego ejecutaremos el Procedimiento Almacenado stpExibe_Pessoa, informando la @variable 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:

Y ahora, viendo el código completo de una vez:
Ver código fuente

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

Es importante tener en cuenta que, después de crear el tipo y usarlo en un Procedimiento almacenado, por ejemplo, solo podrá eliminar el tipo si está desasociado del SP. De lo contrario, recibirás este mensaje de error al intentar eliminarlo (recordando que no es posible cambiar el tipo con estructura de tabla):

Mensaje 3732, Nivel 16, Estado 1, Línea 2
No se puede eliminar el tipo 'dbo.tpPessoa' porque el objeto 'stpExibe_Pessoa' hace referencia a él. Puede haber otros objetos que hagan referencia a este tipo.

Tipos con estructuras de tablas más complejas

Ahora, probemos un ejemplo un poco más complejo con algunas reglas comerciales:

Ver código fuente

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

Al intentar ejecutar este código anterior, nos encontraremos con el siguiente mensaje de error, que demuestra que el índice ÚNICO que creamos en el tipo realmente está funcionando:

Mensaje 2627, Nivel 14, Estado 1, Línea 55
Violación de la restricción CLAVE ÚNICA 'UQ__#A8CD763__C7F1EF4BE5A1C81E'. No se puede insertar una clave duplicada en el objeto 'dbo.@Variavel_Pessoa'. El valor de clave duplicado es (1111111).
La declaración ha sido cancelada.

Después de corregir los datos de entrada (reemplacé el documento “1111111” con “4444444”) y ejecutar el script nuevamente, vemos que devuelve este conjunto de datos:

Comparando con el resultado anterior, vemos que esta vez está ordenando los resultados por Nombre, ya que declaramos un índice agrupado en el campo Nombre. Aunque el índice agrupado no garantiza el orden de los registros, vimos que se está poniendo en práctica en el tipo tpPessoa.

¿Aún no estás convencido de que el índice realmente exista y se esté utilizando en el tipo tpPessoa? ¡DE ACUERDO!

¿Qué pasa si intentamos crear un índice después de crear el tipo tpPessoa?

CREATE NONCLUSTERED INDEX SK01_tpPessoa ON dbo.tpPessoa(Nr_Documento)

Resultado:

Mensaje 1088, Nivel 16, Estado 12, Línea 15
No encuentro el objeto “dbo.tpPessoa” porque no existe o no tienes permisos.

¿Qué pasa con OLTP en memoria? ¿Funciona? ¡¡Sí!!

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

Usar tipos estructurados en tablas en funciones

Hasta entonces, demostré cómo usar tpPessoa en procedimientos almacenados. ¿Pero es posible usarlo en funciones?

Probemos:
Ver código

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)

Resultado: (¡funcionó!)

¿Qué pasa si lo probamos en funciones con valores de tabla?
Ver código

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: (¡funcionó!)

¡Eso es todo, amigos!
Espero que hayas disfrutado de esta publicación y ¡hasta la próxima!

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

Dirceu Resende

Arquitecto de Bases de Datos y BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.

WhatsApp Telegram Ver mis certificaciones

Comentários (0)

Carregando comentários…