SQL Server - How to Pass a Table as a Parameter for Stored Procedures and Functions

Views: 7.896 views

In this article

Reading Time: 7 minutes

Speak guys!
In this post today I would like to demonstrate a feature available since SQL Server 2008 and that I see very few use cases in everyday life in the companies I worked for, which is the use of "tables" as parameters for Stored Procedures.

Introduction

If you're a guy who likes gambiarras, I'm sure you thought I was referring to passing the table name as a parameter of type VARCHAR and using dynamic query to read the table data, right? Yeah, but it's not about that post no .. lol

The purpose of this article is to show how to use table table types to pass as parameter of Stored Procedures complex data types by simulating a table even from the database.

Using this type of object has a number of advantages to our programming:

Do not generate locks
They are cached, as are temporary tables.
Simplifies programming
Brings business rules for data modeling
Provides a strong typing template
Provides tables and structured data for Stored Procedures
Can be created using In-Memory OLTP = WITH (MEMORY_OPTIMIZED = ON)

However, we must also pay attention to the restrictions:

SQL Server does not maintain statistics on table-valued parameter columns.
Table-valued parameters must be passed as READONLY input parameters for Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
You cannot use a table-valued parameter as the target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO, a string, or INSERT EXEC of Stored Procedures.
A table type cannot be changed. If you need to change it, you will need to recreate it. If it is used as parameters in objects, the type must be disassociated from these objects, deleted and then created again with the desired structure.
You cannot create indexes directly on table-type type (below I will demonstrate how to create indexes)

Creating our first table structure type

The first step to this is to create our table type data type:

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

CREATE TYPE dbo.tpPessoa AS TABLE (

Nome varchar(100),

Idade INT,

Dt_Nascimento DATE

)

Now, let's create a simple Stored Procedure, which will get a @variable of type tpPeople, which we just created and will simply show this data on the screen:

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

    SELECT *
    FROM @Pessoa

END
GO

CREATE PROCEDURE dbo.stpExibe_Pessoa (

@Pessoa tpPessoa READONLY

)

BEGIN

SELECT *

FROM @Pessoa

END

After creating this Stored Procedure, we will declare an @variable using the type tpPerson, enter some values, and then execute the Stored Procedure stpExibe_Persona, entering the @variable as a parameter:

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

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

Result:

And now, viewing the code all at once:
View source

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

-- Cria o tipo tpPessoa

CREATE TYPE dbo.tpPessoa AS TABLE (

Nome varchar(100),

Idade INT,

Dt_Nascimento DATE

)

-- Cria uma procedure que você irá receber como parâmetro uma variável do tipo tpPessoa

CREATE PROCEDURE dbo.stpExibe_Pessoa (

@Pessoa tpPessoa READONLY

)

BEGIN

SELECT *

FROM @Pessoa

END

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

It is important to note that once the type is created and used in a Stored Procedure, for example, you will only be able to delete the type if it is unlinked from SP. Otherwise, you will get this error message when you try to delete it (remembering that you cannot change the table structure type):

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.

Types with more complex table structures

Now, let's try a slightly more complex example with some business rules:

View source

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

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

)

-- Cria uma procedure que você irá receber como parâmetro uma variável do tipo tpPessoa

CREATE PROCEDURE dbo.stpExibe_Pessoa (

@Pessoa tpPessoa READONLY

)

BEGIN

SELECT *

FROM @Pessoa

END

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

When trying to execute this code above, we will come across the error message below, which proves that the UNIQUE index we created in the type is really working:

Msg 2627, Level 14, State 1, Line 55
Violation of UNIQUE KEY constraint 'UQ__#A8CD763__C7F1EF4BE5A1C81E'. Cannot insert duplicate key in object 'db[email protected]_Person'. The duplicate key value is (1111111).
The statement has been terminated.

After correcting the input data (I replaced the document “1111111” with “4444444”) and running the script again, we see that it returns this data set to us:

Comparing with the previous result, we see that this time it is sorting the results by Name, since we declare a clustered index in the Name field. Although the clustered index does not guarantee the ordering of records, we have seen that it is being put into practice in the tpPerson type.

Still not convinced that the index really exists and is being used in the type tpPeople? OK!

What if we try to create an index after the type tpPeople has been created?

CREATE NONCLUSTERED INDEX SK01_tpPessoa ON dbo.tpPessoa(Nr_Documento)

1	CREATE NONCLUSTERED INDEX SK01_tpPessoa ON dbo.tpPessoa(Nr_Documento)

Result:

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

What about In-Memory OLTP? It works? Yes!!

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

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)

Using Table Structure Types in Functions

Until then, I demonstrated how to use tpPerson in Stored Procedures. But is it possible to use it in functions?

Lets test:
View code

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)

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

)

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

BEGIN

RETURN (SELECT TOP(1) Nome FROM @Pessoa ORDER BY Idade DESC)

END

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

Result: (it worked!)

What if we try table-valued functions?
View code

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)

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

)

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

RETURN

SELECT Nome, YEAR(Dt_Nascimento) AS Ano_Nascimento, MONTH(Dt_Nascimento) AS Mes_Nascimento, DAY(Dt_Nascimento) AS Dia_Nascimento

FROM @Pessoa

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

Result: (it worked!)

That's it folks!
I hope you enjoyed this post and see you next time!

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

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

SQL Server - How to Pass a Table as a Parameter for Stored Procedures and Functions

Introduction

Creating our first table structure type

Types with more complex table structures

Using Table Structure Types in Functions

You may also like ...

1 Response

Leave a Comment Cancel reply

Introduction

Creating our first table structure type

Types with more complex table structures

Using Table Structure Types in Functions

You may also like ...

Performing POST and GET requests using CLR (C #) in SQL Server

SQL Server - How to identify the data type of columns and parameters of Tables, Views, Stored Procedures and Functions

SQL Server - How to Batch All .sql Scripts in a Folder or Directory by SQLCMD

1 Response

Leave a Comment Cancel reply