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)
SupplierId INT, 
SupplierName VARCHAR(100),		
SupplierEmail VARCHAR(50)
INSERT INTO dbo.Supplier (SupplierId,SupplierName,SupplierEmail) 

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

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

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 
EXEC LoadSupplierInfo @TV_Supplier

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: