Table Type and Table Valued Parameters

What is User-Defined Table Types

User-Defined Table Types (UDTTs) is a kind of user defined data type its can be pass as a parameter in procedure where we need to pass multiple rows and columns.

First need to create as a user-defined table type. Before passing in parameter we need to create as a variable as a UUDTT and this one variable pass in procedure parameter.

— create table for demo
create table tmptable (id int ,name varchar(100) ,code varchar(100));
GO

— insert some records for demo
insert into tmptable
VALUES(1001,’abc’,’001′),(1002,’pqr’,’002′),(1003,’adfdf’,’003′)

GO

–create user defined table type
create type tbl as table (ID int,code varchar(100))
GO

–create proc with UDTT parameter
CREATE proc sp_UDTT_demo
(
@tbl tbl readonly
)
as
begin
select * from tmptable a
where EXISTS (select id from @tbl z where z.id=a.id or z.code=a.code)
end
GO

— declare variable for stored procedure parameter
declare @tbl_1 as  tbl
insert into @tbl_1
VALUES(1001,’001′),(1003,’003′)
 
— execute stored procedure
exec sp_UDTT_demo  @tbl_1;