User Defined Table Type

Drop Proc spGetColumnDetailsForTable 
GO
Drop Type CustParam


--User defined Type {Table}
Create Type CustParam as Table
(
Name varchar(100),
ID int
)

Go

-- SP to have UDT as Parameter . Readonly Keyword is Compulsary when UDT is used
Create Proc spGetColumnDetailsForTable  @Para CustParam  READONLY
as

-- Below Syntax when you need to read Values from UDT
--This return only First Value in Data Set
--Declare @Name as Varchar(100)
--Select @Name=Name  From @Para
--Select @Name


--This return only First Value in Data Set
--Select * From sys.objects  objs  Where  objs.name in ( @Name)


-- UDT is Used as Table to Hit join
Select * From sys.objects  objs  
inner join @Para Pr on Pr.id  = objs.object_id



GO



--- Sample for using UDT 
Declare @T as CustParam

Insert into  @T (Name,ID)
Select name,object_id From Sys.Objects Where name Like 'T%'

---Select * from @T
Exec spGetColumnDetailsForTable @T