With the introduction of SQL Server 2008 came the ability to define an entire table as a parameter, think of it like a table data type. This feature greatly eases the development process as constructing and parsing XML data strings is no longer necessary.
Table parameters are user defined parameters, i.e. you are creating a means of storing specific data that is passed by a stored procedure or function.
- The READONLY clause must be used when passing in the table valued variable
- Data in the table variable cannot be modified
- The table variables cannot be used as OUTPUT parameters only input parameters.
- When data is passed to the table variable the table variable must be passed to the stored procedure in the same batch. Table variables go out of scope as soon as the procedure or batch returns.
The following is a complete end to end example of how to create and pass data to table parameters:
If you want to view other types of table type definitions in your system, or you’ve forgotten what you called a specific table parameter, you can execute the following query, which looks in the system catalog: