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

Views: 4.208
Reading Time: 7 minutes

Hey guys!
In this post today I would like to demonstrate a feature available since SQL Server 2008 that I see very few day-to-day use cases in the companies I worked with, 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:

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:

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:

Result:

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

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.tpPeople' because it is being referenced by object 'stpExibe_People'. 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

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 '[Email protected]riavel_Pessoa '. The duplicate key value is (1111111).
The statement has been terminated.

After correcting the input data (replacing document “1111111” with “4444444”) and rerunning the script, we see that it returns this dataset 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?

Result:

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

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

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

Result: (it worked!)

What if we try table-valued functions?
View code

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