You are here:Home » tsql » Table-Valued Parameter - New feature of SQL Server 2008

Table-Valued Parameter - New feature of SQL Server 2008


In the past, during development of the stored procedures, I have used XML or VARCHAR data type with comma separated data values as parameter of a stored procedure. This was the option before SQL Server 2008 arrived whenever we required to use it.

After releasing the SQL Server 2008, it has another alternative option instead of I used earlier. That option is "Table-Valued Parameter". We can use this option with tsql statements and with stored procedures as well. Really amazing future.
IF EXISTS (SELECT
             1
           FROM   sys.types
           WHERE  name = 'TabbleValued')
  DROP TYPE TableValued

GO

IF ( Object_id('TableValuedInsert') > 0 )
  DROP TABLE TableValuedInsert

GO 
-- Creating Type
CREATE TYPE TableValued AS TABLE 
(
TypeId INT, 
TypeName VARCHAR(100)
)

GO 

-- Creating table
CREATE TABLE TABLEVALUEDINSERT
(
TYPEID INT,
TYPENAME VARCHAR(100)
)
GO
Let us insert records into table using table- values parameters with t-SQL statements,
DECLARE @NewTypeTable TABLEVALUED

INSERT INTO @NewTypeTable
VALUES      (1,'Type-1'),
            (2,'Type-2'),
            (3,'Type-3'),
            (4,'Type-4'),
            (5,'Type-5') 

INSERT INTO TABLEVALUEDINSERT
SELECT
  TYPEID
  ,TYPENAME
FROM   @NEWTYPETABLE

GO 
-- Viewing data in table 
SELECT
  TYPEID
  ,TYPENAME
FROM   TABLEVALUEDINSERT

GO 

Now we will insert records into table using table- values parameters with stored procedure,
-- Creating stored procedure
CREATE PROCEDURE Ins_TableValuedData
(@NewTypeTable TableValued READONLY)
AS
BEGIN
SET nocount ON
INSERT INTO TableValuedInsert
SELECT
  TypeId
  ,TypeName
FROM   @NewTypeTable 

END
GO
Now running stored procedure, before going ahead truncate table,
TRUNCATE TABLE TABLEVALUEDINSERT

GO 
DECLARE @NewTypeTable TableValued
INSERT INTO @NewTypeTable
values  (6,'Type-6'),
  (7,'Type-7'),
  (8,'Type-8'),
  (9,'Type-9'),
  (10,'Type-10')

EXEC INS_TABLEVALUEDDATA @NEWTYPETABLE
GO
-- Veiwing Data 
SELECT
  TYPEID
  ,TYPENAME
FROM   TABLEVALUEDINSERT

GO 

No records in a table using table- values parameters with t-SQL statements,
DECLARE @NewTypeTable TABLEVALUED

INSERT INTO @NewTypeTable
VALUES      (6,'Type-11'),
            (7,'Type-12'),
            (8,'Type-13'),
            (9,'Type-14'),
            (10,'Type-15') 


UPDATE a
SET    a.TypeName = b.TypeName
FROM   TableValuedInsert a
       INNER JOIN @NewTypeTable b
         ON ( a.TypeId = b.TypeId )

GO
-- Viewing data
SELECT
  TypeId
  ,TypeName
FROM   TableValuedInsert

GO 

There is some limitation there with this feature like we need to pass the parameter in store procedure as read only, So can not perform DML operations within on this table type stored procedure. Also, it can not be used with INSERT INTO and INSERT EXEC statements.

Any way it is very useful for the SQL developers. Happy Development!