Demo Script
Table-Valued Parameters
Lab version: 1.0.0
Last updated: 12/1/2011
CONTENTS
OVERVIEW ................................................................................................................................................... 3
Key Messages ........................................................................................................................................... 3
Key Technologies ...................................................................................................................................... 3
Time Estimates ......................................................................................................................................... 3
SETUP AND CONFIGURATION .................................................................................................................. 4
Task 1 – Running the Configuration Wizard ......................................................................................... 4
OPENING STATEMENT............................................................................................................................... 6
STEP-BY-STEP WALKTHROUGH .............................................................................................................. 7
Segment #1 – Using Table-Valued Parameters ....................................................................................... 7
SUMMARY .................................................................................................................................................. 14
Overview
This document provides setup documentation, step-by-step instructions, and a written script for demonstrating table-valued parameters in SQL
Server 2008.
Key Messages
1. Table-valued parameters are an efficient mechanism that allows you to send a batch of data in a single round trip. You can use table-
valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function,
without creating a temporary table or many parameters.
2. Table-valued parameters are similar to parameter arrays in OLE DB and ODBC, but offer more flexibility and closer integration with
Transact-SQL and can participate in set-based operations.
3. Table -valued parameters are strongly typed.
4. 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.
Key Technologies
This demo uses the following technologies:
1. Microsoft SQL Server 2008 R2 Express (or higher)
Time Estimates
Estimated time for setting up and configuring the demo: 0 min
Estimated time to complete the demo: 10 min
Setup and Configuration
System Requirements
Microsoft SQL Server 2008 R2 Express (or higher)
Microsoft SQL Server Management Studio
After completing the demo, you can run the Cleanup.cmd script to revert any changes made to your system. If you intend to present other
demos in the training kit, avoid running this script until you have completed them (the script will drop the SQLTrainingKitDB database used
elsewhere in the training kit).
Task 1 – Running the Configuration Wizard
The following steps describe how to run the Configuration Wizard tool included with the demo to verify that all the prerequisites are properly
installed.
1. Browse to the setup folder in the Source folder of this demo, and run the Setup.cmd script. This script will launch the Configuration
Wizard for the demo. The Configuration Wizard is designed to check your computer to ensure that it is properly configured with all of
the dependencies needed to run the demo.
2. Click through the steps in the Configuration Wizard to get to the Detecting Required Software step; a scan of prerequisites will be
performed on your computer. If you do not have the necessary dependencies, install them using the links provided by the tool and
rescan your computer.
Figure 1
Dependencies check completed successfully
3. Once the required software is properly installed click Next to configure your computer for this demo. A script will be executed to create
the server alias ('SQLServerTrainingKitAlias') used in this demo to establish a connection with the database.
Figure 2
Configuration Wizard tasks
Opening Statement
In this demo, I will describe the use of table-valued parameters, which is a feature introduced in SQL Server 2008 to solve the problem of
sending multiple rows of data to a stored procedure or function. This requirement is relatively common in database applications and the
canonical example of this is orders and line items, where you have one order that contains N line items. N is variable and can be very large. How
do you pass this information efficiently to a stored procedure?
One database round trip per line item, which is how applications traditionally tackled this in the past, can slow things down. One approach taken
by some applications using SQL Server 2000 and SQL Server 2005 to get around this issue is to compose the order and line items into XML
format. The application passes this information as an XML data type in SQL Server 2005 or as a large text field in SQL Server 2000 and then,
inside a stored procedure, SQL Server decomposes the XML into relational rowsets using built-in functions. This method works but is not
optimal, as XML composition and decomposition takes time. Table-valued parameters are intended to solve this problem.
Even though table variables are not new to SQL Server, SQL Server 2008 introduces the ability to use them as strongly typed parameters to
stored procedures and functions. I will now show you how you can use this feature.
Step-by-Step Walkthrough
This demo is composed of the following segments:
1. Using Table-Valued Parameters
Note: The SQL statements shown in the Action column provide the necessary context for a demo step. The SQL demo script file in the code
folder may contain additional statements, where appropriate, to verify the existence and conditionally create required database objects. These
statements have been omitted in the document for simplicity.
Segment #1 – Using Table-Valued Parameters
Action Script Screenshot
1. In SQL Server Management I will now demonstrate the use of
Studio, open the table-valued parameters. To do this, I
TableValuedParameters.sql file will create a new database named
located under the code folder in the SqlTrainingKitDB, which we will use
Source folder of this demo. for the remainder of this demo.
2. Select the CREATE DATABASE
statement and click Execute ( ) to
create the SqlTrainingKitDB
database.
3. Select the USE statement and click
Execute ( ) to use the new
SqlTrainingKitDB database.
T-SQL
CREATE DATABASE SqlTrainingKitDB
GO
USE SqlTrainingKitDB
GO
4. Select both CREATE TABLE First, I will create an order table and a
statements and click Execute ( ) to line item table, named TVPOrder and
create the TVPOrder and TVPItem TVPItem respectively. You would
tables. typically find similar tables in any
application that manages orders.
T-SQL
CREATE TABLE dbo.TVPOrder (
CustCode varchar(5),
OrdNo int identity,
OrdDate datetime
)
GO
CREATE TABLE dbo.TVPItem (
OrdNo int,
ProdCode int,
Qty int
)
GO
5. Select the first CREATE I will now show you how applications
PROCEDURE statement and click traditionally handled this in previous
Execute ( ) to create the versions of SQL Server.
TVPOrderInsert stored procedure.
To do this, we will create a stored
6. Select the second CREATE procedure named TVPOrderInsert
PROCEDURE statement and click that inserts order information into the
Execute ( ) to create the orders table. This procedure receives
TVPItemInsert stored procedure. a customer code as a parameter. It
inserts a new row into the orders
T-SQL table and returns - as output
CREATE PROCEDURE parameters - the inserted order
dbo.TVPOrderInsert number and the order date.
(
@CustCode varchar(5), In addition, we will create another
@OrdNo integer output, stored procedure named
@OrdDate datetime output) TVPOrderInsert that inserts a line
AS item for an order. Notice that you
need to call this procedure once for
SET @OrdDate = GETDATE();
each line item in the order.
INSERT INTO dbo.TVPOrder
(OrdDate, CustCode)
VALUES (@OrdDate, @CustCode);
SELECT @OrdNo =
SCOPE_IDENTITY()
GO
CREATE PROCEDURE
dbo.TVPItemInsert
(
@OrdNo integer,
@ProdCode integer,
@Qty integer)
AS
INSERT INTO dbo.TVPItem (OrdNo,
ProdCode, Qty)
VALUES (@OrdNo, @ProdCode,
@Qty)
GO
7. Select the DECLARE, EXEC, and Inserting a new order that contains
SELECT statements and click three line items requires several
Execute ( ) to insert an order steps. First, you need to call
containing three line items into the TVPOrderInsert to insert the order
database. Show the tables content. row. Then, you need to make three
calls to TVPItemInsert to insert each
T-SQL line item.
DECLARE @ordno int;
DECLARE @orddate datetime; Even though this approach succeeds
and is the way that an application
EXEC dbo.TVPOrderInsert 'ALFKI', might have handled this task using
@ordno output, @orddate output previous versions of SQL Server,
notice that it requires four database
EXEC dbo.TVPItemInsert @ordno, round trips to fulfill the objective.
111, 1
EXEC dbo.TVPItemInsert @ordno,
222, 2
EXEC dbo.TVPItemInsert @ordno,
333, 3
GO
SELECT * FROM dbo.TVPOrder;
SELECT * FROM dbo.TVPItem;
GO
8. Select the CREATE TYPE statement I will now show you how you can
and click Execute ( ) to create the significantly optimize this process by
TVPParam user-defined table type. using table-valued parameters.
T-SQL
First, we need to create a new user-
IF (SELECT COUNT(*) FROM
sys.table_types defined table type. Table types
WHERE name = 'TVPParam' AND represent the definition of a table
schema_id = 1) = 0 structure that you can use to declare
table-valued parameters for stored
CREATE TYPE dbo.TVPParam AS
procedures and functions or to
TABLE(ProdCode integer, Qty declare table variables. We will create
integer) a new table type named TVPParam
GO with two columns: a product code and
a quantity, and we will use it to store
line items.
9. Highlight each line of the Next, we need to restructure our
TVPOrderEntry procedure as you previous order entry mechanism by
describe it. replacing the TVPOrderInsert and
TVPItemInsert stored procedures with
10. Select the CREATE PROCEDURE a single stored procedure named
TVPOrderEntry.
statement and click Execute ( ) to
create the TVPOrderEntry stored
procedure. Notice that the TVPOrderEntry
procedure receives a table-valued
T-SQL parameter named Items that has a
CREATE PROCEDURE TVPParam data type, which is the
dbo.TVPOrderEntry table type that we defined previously.
( In addition, the procedure defines this
@CustCode varchar(5), parameter as read-only. This is
required for table-valued parameters.
@Items TVPParam READONLY,
@OrdNo integer output,
@OrdDate datetime output) In its body, the stored procedure
AS inserts a new row into the order table.
Next, it determines the order number
SET @OrdDate = GETDATE();
of the newly inserted order by
retrieving the current scope identity.
INSERT INTO TVPOrder (OrdDate, Finally, it inserts the order's line items
CustCode) by selecting all the rows from the
VALUES (@OrdDate, @CustCode); table-valued parameter.
SELECT @OrdNo = We will now create the stored
SCOPE_IDENTITY(); procedure and test it.
INSERT INTO TVPItem (OrdNo,
ProdCode, Qty)
SELECT @OrdNo, ProdCode, Qty
FROM @Items
GO
11. Select the DECLARE, INSERT To do this, we need to declare a table
INTO, and EXEC statements and variable that has a TVPParam data
click Execute ( ) to insert an order type.
containing three line items into the
database. Next, we insert three rows that
T-SQL contain the line items of the order into
this table. Notice that we are using
DECLARE @ordno int;
the new Transact-SQL row
DECLARE @orddate datetime; constructor feature to insert all three
DECLARE @t TVPParam ; rows in a single statement.
INSERT INTO @t VALUES(444, 4),
(555,5), (666,6);
Finally, we call the TVPOrderEntry
EXEC dbo.TVPOrderEntry 'BEAUC', stored procedure passing in a
@t, @ordno output, @orddate customer code and the table variable
output; that contains the order's line items.
GO Notice that by using this procedure,
we can insert the order and its line
items in a single round trip.
12. Highlight both SELECT statements
and click Execute ( ) to display the
contents of the TVPOrder and We can now retrieve the contents of
TVPItem tables. the TVPOrder and TVPItem tables
and see that the stored procedure
T-SQL has successfully inserted the order
SELECT * FROM dbo.TVPOrder; and its line items.
SELECT * FROM dbo.TVPItem;
GO It is important to remember that table-
valued parameters are materialized in
tempdb. You must consider this if you
are going to send large amounts of
information.
13. Highlight both SELECT statements We can examine the contents of the
and click Execute ( ) to show the sys.types table. This table contains a
content of the sys.types and row for each system and user-defined
sys.table_types tables. type. We can see our TVPParam
user-defined table type included in
T-SQL this list.
SELECT * FROM sys.types
GO Similarly, we can view metadata for
SELECT * FROM sys.table_types the user-defined table types only by
GO querying the sys.table_types table
and you can see the TVPParam type
here as well.
14. Scroll to the row containing the
TVPParam user-defined table type
definition and highlight the
is_table_type column.
15. Select the CREATE FUNCTION You can use table-valued parameters
statement and click Execute ( ) to in functions too. I will show you an
create the TVPFunction function. example that uses a very simple
function that receives a table-valued
T-SQL parameter and returns the count of
CREATE FUNCTION TVPFunction (@t rows that it contains.
AS dbo.TVPParam READONLY)
RETURNS int To use this function, we need to
AS declare a table variable using our
BEGIN TVPParam table data type. First, we
RETURN (SELECT count(1) FROM call our function passing in the table
@t) variable. Notice that because we
END have not inserted any rows into the
table, the function returns a count of
GO
zero.
16. Highlight the DECLARE, INSERT,
and SELECT statements and click
Execute ( ) to show the number of Next, we insert a couple of rows into
rows in the table-variable. the table using row constructors and
call the function again. You can see
T-SQL that it now returns a count of two.
DECLARE @t AS dbo.TVPParam
SELECT dbo.TVPFunction(@t)
INSERT INTO @t VALUES
(1,1),(2,2)
SELECT dbo.TVPFunction(@t)
GO
17. Highlight the DECLARE, EXEC, Finally, notice that the store
and SELECT statements and click procedure still works if we do not
Execute ( ) to show the number of specify the table-valued parameter.
rows in the table-variable. By querying the contents of the
tables, we can see that a record is
T-SQL inserted in the TVPOrder table, but
DECLARE @ordno int; no line item is inserted in the
DECLARE @orddate datetime; TVPItem table.
EXEC dbo.TVPOrderEntry
'BFLKU',@OrdNo = @ordno output,
@Orddate=@orddate output;
SELECT * FROM dbo.TVPOrder;
SELECT * FROM dbo.TVPItem;
Summary
In this demo, you have seen how to use table-valued parameters in SQL Server 2008 to send multiple rows of data to Transact-SQL statements or
routines, without creating temporary tables or additional parameters. You have learnt how to declare user-defined table types and use them to
define strongly typed table variables, and how to pass these table variables as parameters to stored procedures and functions. You were able to
compare alternative approaches and see how table-valued parameters provide and efficient solution that minimizes the number of round trips
required to send multiple rows of data.