User Defined Table (UDT) types and their usage in procedures

This is a simple example which shows how to create a data type and how it can be used to pass an array of data as an input parameter to a stored procedure.

create table Customer
(
CustomerId int identity(1,1) primary key,
CustomerCode nchar(3),
CustomerName nvarchar(50)
)
go

if type_id(‘udt_Customer’) is not null
drop type udt_Customer;

create type udt_Customer as table
(
CustomerCode nchar(3),
CustomerName nvarchar(50)
);
go

create procedure proc_CustomerInsert
(
@customer udt_Customer readonly
)
as
begin

insert Customer (CustomerCode,CustomerName)
select CustomerCode,CustomerName
from   @customer

select * from Customer

end
go

———
declare @customer udt_Customer

insert @customer(CustomerCode,CustomerName)
select ‘001’,’Ethan Hunt’
union
select ‘002’,’James Bond’

exec proc_CustomerInsert @customer
go