SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.




download SQL Server 2014



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:

User Defined Table Types

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





Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers









Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems