SQL Server 2008 - Table-Valued Parameter

It has always been a problem in earlier versions to pass multi row –multi column (record set) parameter from a Stored procedure or a function. In 2008 this problem is solved and now you can pass Table variable as parameter between stored procedures and functions.

How it works?
The first step in this is to create a User Defined Table datatype in the database. Once you created the User Defined datatype(UDT) of table you can declare a variable of that UDT as input parameter of a sp or function.

(a) Create a User TABLE Type

CREATE TYPE TestTableTypeParm AS TABLE (EmpID int primary key,
EmpType varchar(10),
EmpName varchar(50),
CHECK (EmpType IN ('MNGR', 'CLRK', 'TECH') ))


(b) Create stored procedure with a Table type as input parameter.
Create PROCEDURE spTestTableTypeParm

(@TVP TestTableTypeParm READONLY,
@someotherparam int )
As
Select *From @TVP

(c) How to call this stored procedure

Declare @TVP as TestTableTypeParm
Insert @TVP select 1,'MNGR','aaaa'
Insert @TVP select 2,'CLRK','bbb'
Insert @TVP select 3,'TECH','ccc'

Exec spTestTableTypeParm @TVP,1


(d) Create a function with table type input parameter

CREATE FUNCTION ufn_TestTablevaluedParameter(@TVP TestTableTypeParm READONLY)
RETURNS TABLE
AS
RETURN
(
select *From @TVP
);
GO

(f) How to call function with tabletype input parameter

Declare @TVP as TestTableTypeParm
Insert @TVP select 1,'MNGR','aaaa'
Insert @TVP select 2,'CLRK','bbb'
Insert @TVP select 3,'TECH','ccc'

Select *From ufn_TestTablevaluedParameter(@TVP)



There are many Limitations mentioned in Books online. These are probably most significant among them

(a) SQL Server does not maintain statistics on columns of table-valued parameters.

(b) Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

(c) You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored-procedure.

(d) A user-defined table type cannot be used as a column in a table or a field in a structured user-defined type.

(e) A DEFAULT value cannot be specified in the definition of a user-defined table type.

(f) The user-defined table type definition cannot be modified after it is created.

(g) User-defined functions cannot be called within the definition of computed columns of a user-defined table type.

Summary

(a) We can use declare table-valued variables within dynamic Transact-SQL statements and pass these variables as table-valued parameters to stored procedures and functions.
(b) The table valued variable data can not be modified inside the object where its referred. It has to be READONLY.
© In SQL Server 2000, it was not allowed to insert the output of an stored procedure to a table type variable. This was solved in SQL Server 2005 and it was possible to insert the output of an sp to table variable. But Table-Valued parameter has again have this limitation. You can not use in Select Into or INSERT EXEC.
(d) sys.table_types : This system object will retrun all the Table types in the database.
select *from sys.table_types