Table type in sql

Table type is introduced in SQL 2008. Table type will be useful to send multiple records to a store procedure or function and it can be used to declare a table variable.

The example used here will illustrate the practical uses of table type. I want to load supplier data to table, for this purpose, a stored procedure will be created which will take one or more record as input and perform some kind of validation and load the records into table.

Create table and table type for supplier

CREATE TABLE dbo.Supplier
(
SupplierId INT, 
SupplierName VARCHAR(100),
SupplierEmail VARCHAR(50)
);
CREATE TYPE TV_Supplier AS TABLE 
(
SupplierId INT, 
SupplierName VARCHAR(100),		
SupplierEmail VARCHAR(50)
);
INSERT INTO dbo.Supplier (SupplierId,SupplierName,SupplierEmail) 
VALUES(1,'BSA','warehouse@bsa.com'),(2,'Honda','warehouse@honda.com');

Create store procedure to load records which are new and not already exists in supplier table.

CREATE PROCEDURE dbo.LoadSupplierInfo
(
@SupplierDtl TV_Supplier READONLY
)
AS
BEGIN
	INSERT INTO dbo.Supplier
	SELECT S1.* FROM @SupplierDtl s1 LEFT JOIN dbo.Supplier s2
	ON s1.SupplierName = s2.SupplierName
	WHERE s2.SupplierName is null
END

The type TV_Supplier created in the previous step used in stored procedure as a data type for the parameter, additionally a keyword READONLY is mentioned beside the type name, this is required as the table-valued parameter “@SupplierDtl” cannot be modified.
To pass records to procedure first declare a variable as table type TV_Supplier. The variable is populated with some new records and passed as a parameter to stored procedure.

DECLARE @TV_Supplier TV_Supplier 
INSERT INTO @TV_Supplier
VALUES(3,'Puma','warehouse@puma.com'),(4,'Nike','warehouse@nike.com'), 
(1,'BSA','warehouse@bsa.com')
EXEC LoadSupplierInfo @TV_Supplier