Thursday , March 28 2024

User Defined Table Type and Table Valued Parameter in SQL Server

With User Defined Table Type, we can store the structure of a table. We can use the User Defined Table Typies by defining the table-valued parameter in the Stored Procuder or Functions. We can use these structures instead of specifying the temporay table in the Stored Procedure or Function.

Below you can see the sample usage. You can find explanations in the scripts.

Create User Defined Table Type

Using User Defined Table Type in a Stored Procedure by using table-valued parameter

Table valued parameter in this example is @TVP

Define a variable of the type of table type that we created

Insert data to this variable

Execute the stored procedure by passing this variable as a parameter

Source <https://msdn.microsoft.com/en-us/library/bb510489(v=sql.105).aspx>

Loading

About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories