Table Valued Parameters New In SQL Server 2008 T-SQL Improvements

One of the T-SQL enhancements or improvements introduced with Microsoft SQL Server 2008 is Table Valued Parameters (TVPs).

Table Values Parameters allow MS SQL Server Developers to pass a set of rows as a parameter to a stored procedure.

This is great since if you are following community forums about Microsoft SQL Server and T-SQL, one of the most asked question is about how to pass a set of rows as parameter to a stored procedure.

In the below following t-sql statement code block, I tried to summarize and illustrate how table-valued parameters can be used with in sample sql codes.

The scenario is as simple as selecting a list of Customers into a table valued parameter and sending this set of selected sql rows to a stored procedure which will process each record and update Customer record IsProcessed flag to true.

Of course in order to run the scenario successfully, we have make some preparations.

The sql code block begins with creating the Customers table and populates the new created Customers sample table with some sample fictional data :)

Then we are creating a custom type as table to store our list of customer records or rows.
This is a new t-sql enhancement in Microsoft SQL Server 2008. You will also realize that there is a new sys table named sys.table_types

sys.table_types keeps a view of table valued types in the related database.

Next comes to define the stored procedure which we will call to process the customer data. Important issue in the definition of the stored procedure is adding the new parameter type with READONLY attribute.
Otherwise you will get the following error message from SQL engine:
The table-valued parameter "@NewCustomers" must be declared with the READONLY option.

Then we declare a new parameter which is as in same type as we have declared as table valued type.
Then we have populated this table valued parameter with data.
This is just simple as inserting data with using usual INSERT INTO DML commands.

Then we call the stored procedure passing the table-valued parameter (tvp) as input.

Since the table valued parameters are only allowed as ReadOnly, you can not operate on the table valued parameters in a stored procedure using DML command statements like INSERT, UPDATE, DELETE.
But you can read from table-valued parameters using SELECT statements and join table-valued parameters to other tables.

If you tracked the t-sql codes, you will realize that only the last section of the sql statement block manages all the scenerio.


IF OBJECT_ID('Customers') IS NULL

BEGIN

CREATE TABLE Customers

(

CustomerId int not null identity(1,1),

[Customer Name] nvarchar(100),

AddressId int,

Approved bit

);

INSERT INTO Customers ([Customer Name], AddressId) VALUES 

(N'Eralper Yilmaz', 101)

INSERT INTO Customers ([Customer Name], AddressId) VALUES 

(N'Bill Gates', 102)

INSERT INTO Customers ([Customer Name], AddressId) VALUES 

(N'Kylie Minogue', 103)

INSERT INTO Customers ([Customer Name], AddressId) VALUES 

(N'Angelina Jolie', 104)

-- SELECT * FROM Customers

END

GO

You can list all user defined table types by running a SELECT query over the sys.table_types catalog view. sys.table_types is a system view which summarizes properties of user defined table type structures.
Also you can select same list by running


SELECT * FROM sys.types WHERE is_user_defined = 1 AND is_table_type = 1


IF EXISTS (

SELECT * FROM sys.table_types WHERE name = N'CustomersTableType'

)

DROP TYPE CustomersTableType

GO



CREATE TYPE CustomersTableType AS TABLE (

CustomerId int,

CustomerName nvarchar(100),

AddressId int

)



IF OBJECT_ID('Process_New_Customers') IS NOT NULL

DROP PROC Process_New_Customers

GO

The table type structure "CustomersTableType" in our custom sample case must be declared with READONLY parameter option in the definition of the Stored Procedure.
If you want to select all the parameters defined as ReadOnly option in the current catalog you can run a SELECT statement on sys.parameters catalog view as follows:


SELECT * FROM sys.parameters WHERE is_readonly = 1

And after executing the above t-sql select query, the returned Object_Id value will point to the stored procedure where this readonly parameter is defined within. You can find the t-sql stored procedure name by running the following sql code :


SELECT OBJECT_NAME(object_id) FROM sys.parameters WHERE is_readonly = 1

And one of the most useful tips on user defined table type objects is I believe the select query which will list all the columns that table structured type has. To get a list of all columns taking place in the definition of a table type parameter or table type structure can be selected as the following sample sql query:


select * from sys.all_columns where object_id = (select type_table_object_id from sys.table_types)

You can reach the list and the object definitions of user defined table types within a SQL Server 2008 database by drilling down the Programmability node then Types node, and finally openning the User Defined Table Types node as shown in the below screen shot from a MS SQL Server 2008 database Object Explorer window:

In case we do not define the table type parameter with READONLY option in the "CREATE PROCEDURE" statement, the SQL2008 database engine will throw the following exception:


--Msg 352, Level 15, State 1, Procedure Add_New_Customers, Line 1

--The table-valued parameter "@NewCustomers" must be declared with the READONLY option.



CREATE PROC Process_New_Customers

(

@NewCustomers CustomersTableType READONLY

)

AS



DECLARE @CustomerId int

DECLARE CustomerCursor CURSOR FAST_FORWARD FOR SELECT CustomerId FROM @NewCustomers



OPEN CustomerCursor



FETCH NEXT FROM CustomerCursor INTO @CustomerId



WHILE @@FETCH_STATUS= 0

BEGIN



UPDATE Customers SET Approved = 1 WHERE CustomerId = @CustomerId AND LEN([Customer Name]) > 5



FETCH NEXT FROM CustomerCursor INTO @CustomerId

END



CLOSE CustomerCursor

DEALLOCATE CustomerCursor

GO

You can run sp_help stored procedure to get a limited information about the table value parameter (tvp) type


sp_help CustomersTableType

GO

In this sql code block we are defining a new table value parameter (tvp) and insert data rows from Customers table. Then we send this table value parameter TVP to a SQL Server 2008 database stored procedure as a readonly parameter.


SET NOCOUNT ON



DECLARE @NewCustomers CustomersTableType

INSERT INTO @NewCustomers (

CustomerId, CustomerName, AddressId

)
SELECT

CustomerId, [Customer Name], AddressId

FROM Customers



SET NOCOUNT OFF

EXECUTE Process_New_Customers @NewCustomers

GO

You can download the t-sql codes that I used to summarize the new T-SQL enhancement in Microsoft SQL Server 2008, Table Valued Parameters from Kodyaz Files section from Table Valued Parameters Sample T-SQL Codes.

Here is a web cast on Table Valued Parameters in SQL Server 2008 presented by Ashish Jaiman.
I believe you will find this 10 minutes presentation very useful summarizing this new feature of SQL Server 2008, Table Valued Parameters.

SQL Server 2008 - Table valued parameters