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','firstname.lastname@example.org'),(2,'Honda','email@example.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','firstname.lastname@example.org'),(4,'Nike','email@example.com'), (1,'BSA','firstname.lastname@example.org') EXEC LoadSupplierInfo @TV_Supplier