Creating a table type param in SQL Server Stored Procedure

Question

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

Andrey Korneyev · Accepted Answer · 2014-12-01 10:09:44Z

3

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.

answered Dec 1, 2014 at 10:09

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating a table type param in SQL Server Stored Procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related