Creating a table type param in SQL Server Stored Procedure

613 Views Asked by At

Is it possible to declare/create the table type param(TVP) in Stored Procedure itself instead of creating the table value type separately in the schema and then using it in Stored procedure. i.e.,

create procedure proc1(
@table1 table(id int) readonly
)
as 
begin
select top 1 * from sysobjects
end
1

There are 1 best solutions below

0
On BEST ANSWER

From MSDN:

Table-valued parameters are based on strongly-typed table structures that are defined by using Transact-SQL CREATE TYPE statements. You have to create a table type and define the structure in SQL Server before you can use table-valued parameters in your client applications.

So it is clearly said that you can't declare TVP in stored procedure like you want - only by creating as user-defined type.