SQL SERVER 2008 Features of Introduction to Table Valued Parameters 

Ref:

http://blog.sqlauthority.com/2008/08/31/sql-server-table-valued-parameters-in-sql-server-2008/

select * from Contact

CREATE TYPE ContactType as Table(Email nvarchar(300),Firstname nvarchar(300),LastName nvarchar(300))

CREATE PROC INSERTCONTACT

@Ct_tvp ContactType Readonly

as

Insert into Contact(Email,FirstName,LastName)

select * from @Ct_tvp

Declare @Cttvp as  ContactType

Insert into @Cttvp(Email,FirstName,LastName)

values('pub@pubmail.com','pub','mail'),('ssf@wsfs.com','ssf','lrwr')

EXEC INSERTCONTACT @Cttvp

Table-Valued Parameters is a new feature introduced in SQL SERVER 2008. In earlier versions of SQL SERVER it is not possible to pass a table variable in stored procedure as a parameter, but now in SQL SERVER 2008 we can use Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so many parameters.

Table-valued parameters are declared using user-defined table types. To use a Table Valued Parameters we need follow steps shown below:

1. Create a table type and define the table structure
2. Declare a stored procedure that has a parameter of table type.
3. Declare a table type variable and reference the table type.
4. Using the INSERT statement and occupy the variable.
5. We can now pass the variable to the procedure.

For Example,

Let’s create a Department Table and pass the table variable to insert data using procedure. In our example we will create Department table and afterward we will query it and see that all the content of table value parameter is inserted into it.

Department:

CREATE TABLE Department(DepartmentID INT PRIMARY KEY,DepartmentName VARCHAR(30)
)GO

1. Create a TABLE TYPE and define the table structure:

CREATE TYPE DeptType AS TABLE(DeptId INT, DeptName VARCHAR(30)
);GO

2. Declare a STORED PROCEDURE that has a parameter of table type:

CREATE PROCEDURE InsertDepartment@InsertDept_TVP DeptType READONLY
AS
INSERT INTO Department(DepartmentID,DepartmentName)SELECT * FROM @InsertDept_TVP;

GO

Important points  to remember :

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

–  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.

3. Declare a table type variable and reference the table type.

DECLARE @DepartmentTVP AS DeptType;

4. Using the INSERT statement and occupy the variable.

INSERT INTO @DepartmentTVP(DeptId,DeptName)VALUES (1,'Accounts'),
(2,'Purchase'),
(3,'Software'),
(4,'Stores'),
(5,'Maarketing');

5. We can now pass the variable to the procedure and Execute.

EXEC InsertDepartment @DepartmentTVP;GO

Let’s see if the Data are inserted in the Department Table

Conclusion:

Table-Valued Parameters is a new parameter type in SQL SERVER 2008 that provides efficient way of passing the table type variable than using the temporary table or passing so many parameters. It helps in using complex business logic in single routine. They reduce Round Trips to the server making the performance better.

In SQL Server 2005 and earlier, it is not possible to pass a table variable as a parameter to a stored procedure. When multiple rows of data to SQL Server need to send multiple rows of data to SQL Server, developers either had to send one row at a time or come up with other workarounds to meet requirements. While a VB.Net developer recently informed me that there is a SQLBulkCopy object available in .Net to send multiple rows of data to SQL Server at once, the data still can not be passed to a stored proc.

Possibly the most anticipated T-SQL feature of SQL Server 2008 is the new Table-Valued Parameters. This is the ability to easily pass a table to a stored procedure from T-SQL code or from an application as a parameter.

User-Defined Table Type

When first taking a look at the new table-valued parameters, I thought that using this feature is a bit complicated. There are several steps involved. The first thing to do is to define a table type. If you look at the Types section under Programmability in the 2008 Management Studio, you will see the new “User-Defined Table Types” (Image 1).

Image 1

At the time of this writing, RC0 is the latest release of SQL Server 2008, and the only way to create this new object is with a T-SQL script. I’m not sure it this will change with the released version, known as RTM, but it is not difficult to create the type. I found that there is a right-click option “New User-Defined Table Type…” that creates a template in the Query Window shown in Image 2.

Image 2

By clicking the “Specify Values for Template Parameters” button, a dialog box pops up that will help complete the definition. Image 3 shows the dialog box after some of the values have been modified.

Image 3

After filling in the appropriate values and clicking OK, a CREATE TYPE statement replaces the template. At this point, additional columns and constraints can be added before clicking OK. Here is the code that was generated:

-- ================================
-- Create User-defined Table Type
-- ================================
USE Test
GO

-- Create the data type
CREATE TYPE dbo.MyType AS TABLE 
(
 col1 int NOT NULL, 
 col2 varchar(20) NULL, 
 col3 datetime NULL, 
    PRIMARY KEY (col1)
)
GO

After running the code, the object definition is created and viewable in the “User-Defined Table Type” section (Image 4). You can view the properties there, but not modify them. To modify the type, you will have to drop it and then create it again with the modified definition.

Image 4

Using the User-Defined Table Type

So far, it seems like we have done quite a bit of work, but we only have the type definition created. Like other programmable objects, it will stick around unless it is dropped. To use it in T-SQL code, you must create a variable of the new type and then populate it as you would any other table. Once it is populated, you can use it in other T-SQL statements. Because it is a variable, it goes out of scope automatically when the batch is completed. Notice in the code below that the name of the data type is the same as the type we just created.

DECLARE @MyTable MyType

INSERT INTO @MyTable(col1,col2,col3)
VALUES (1,'abc','1/1/2000'),
 (2,'def','1/1/2001'),
 (3,'ghi','1/1/2002'),
 (4,'jkl','1/1/2003'),
 (5,'mno','1/1/2004')
 
SELECT * FROM @MyTable 

As long as the variable does not go out of scope, you can do just about anything with it that you can do with a regular table variable, such as join another table or be used to populate another table. Like a table variable, you can not modify the table definition.

As I mentioned, the variable is gone once it goes out of scope. For example if you have a T-SQL script that is composed of more than one batch, the variable is only valid within the batch where it was created.

Using the Variable as a Parameter

So far, we haven’t seen anything that is not possible with a regular table variable. The benefit is being able to pass the variable of the new type to a stored procedure. A stored procedure must be created that uses the new type. Here is an example of that along with the code to create a regular table that we will be populating.

USE [Test]
GO

CREATE TABLE [dbo].[MyTable] (
 [col1] [int] NOT NULL PRIMARY KEY,
 [col2] [varchar](20) NULL,
 [col3] [datetime] NULL,
 [UserID] [varchar] (20) NOT NULL
 ) 

GO

CREATE PROC usp_AddRowsToMyTable @MyTableParam MyType READONLY,
 @UserID varchar(20) AS
 
 INSERT INTO MyTable([col1],[col2],[col3],[UserID])
 SELECT [col1],[col2],[col3],@UserID 
 FROM @MyTableParam
 
GO

Notice the READONLY qualifier after the table parameter. This is required because a copy of the user-defined table variable is not passed to the procedure. To be more efficient, a pointer is passed to the proc. Therefore, to eliminate changes to the variable inside the proc that would affect the original, no changes are allowed.

Finally, let’s put it all together and call the stored procedure. In the next code snippet, code from the previous section is used to create and populate the variable.

DECLARE @MyTable MyType

INSERT INTO @MyTable(col1,col2,col3)
VALUES (1,'abc','1/1/2000'),
 (2,'def','1/1/2001'),
 (3,'ghi','1/1/2002'),
 (4,'jkl','1/1/2003'),
 (5,'mno','1/1/2004')

EXEC usp_AddRowsToMyTable @MyTableParam = @MyTable, @UserID = 'Kathi'

SELECT * FROM MyTable

In order for a user to use the User-Defined Table Type, EXECUTE or CONTROL permission must be granted. This is the command to grant permission to a user:

GRANT EXECUTE ON TYPE::dbo.MyType TO TestUser;

Table-Valued Parameters are indeed a new feature in SQL Server 2008.  As the name implies, you can now pass a table type as a parameter to a function or stored procedure.  At a high level the TVP allows you to populate a table declared as a T-SQL variable, then pass that table as a parameter to a stored procedure or function.  The benefit of the TVP is that you can send multiple rows of data to the stored procedure or function rather than having to declare multiple parameters  or possibly use an XML parameter type to handle a variable number of rows.  According to Books on Line, a TVP is an efficient option for up to 1,000 or so rows.

In this tip we are going to gain an understanding of TVP by walking through a simple code sample to demonstrate how to:

• Create a table type that can be passed as a TVP to a function or stored procedure
• Create a stored procedure that uses a TVP
• Declare the table type, populate it with data, and pass it to a stored procedure

Now let's describe our example.  In the extract, transform, and load (ETL) process in our data warehousing applications we typically map source system keys to surrogate keys during dimension processing; we then use the surrogate keys to uniquely identify the dimension rows in the warehouse.  This allows us to retain the complete history of dimension rows, as each change to a dimension row can be stored in a new row with a new surrogate key.  As dimension rows are changed or added, we simply assign a new surrogate key to the source system key and insert a new row into the dimension table.  For more details on surrogate key processing see our earlier tip Handling Slowly Changing Dimensions in SQL Server Integration Services (SSIS) Packages.  When processing fact rows we lookup the surrogate keys for the source system keys in the fact and store the surrogate key in the fact table; our queries join the fact table to the dimension table by the surrogate key.  Since multiple fact tables typically reference a given dimension (e.g. Customer), the  surrogate key lookup provides a good example for using TVP, allowing us to implement the surrogate key lookup one time in a stored procedure, then call it during our ETL process for multiple fact tables.

In addition to simply looking up the surrogate key for a source system key, we also have a situation where a fact table may have a source system key that doesn't exist in a dimension table.  In this case we want to create an inferred member in the dimension; i.e. create a new surrogate key and add it to the dimension then update it later when we get the actual dimension row from the source system.  For more details on inferred member processing for a dimension, take a look at our earlier tip Handling Early Arriving Facts in SQL Server Integration Services (SSIS) Packages.

The demo code below was only tested on the February, 2008 Community Technology Preview (CTP) of SQL Server 2008. 

Create a Table Type

In order to pass a table as parameter to a stored procedure or function, you first create a TABLE TYPE as follows:

CREATE TYPE SourceKeyList AS TABLE ( SourceKey NVARCHAR(50) ) GO

The T-SQL code is very similar to creating an ordinary table.  You can query sys.types in the current database to determine any table types that have been created:

SELECT name, system_type_id, user_type_id FROM sys.types WHERE is_table_type = 1

Create a Stored Procedure with a TVP

We are going to create a stored procedure that performs the surrogate key lookup and adds an inferred member if the source key doesn't exist.  First we need to create a sample dimension table:

CREATE TABLE dbo.dim_Customer ( sk_Customer INT IDENTITY NOT NULL, CustomerSourceKey NVARCHAR(50) NOT NULL, CustomerName NVARCHAR(50) NOT NULL, InferredMember BIT NOT NULL )

The surrogate key is an integer type and we use the IDENTITY property to automatically assign the next sequential number when inserting rows.  The InferredMember column gets set to 1 when we insert a row for a source key that doesn't exist.  When the row is extracted from the source system during dimension processing, the inferred row is updated with the columns from the source and the InferredMember column is set to 0.

Now let's create a stored procedure that takes our table type as a parameter and performs the surrogate key lookup and inferred processing:

CREATE PROCEDURE dbo.stp_GetCustomerSK @source_key_list SourceKeyList READONLY AS BEGIN INSERT INTO dbo.dim_Customer( CustomerSourceKey, CustomerName, InferredMember ) SELECT SourceKey, N'INFERRED', 1 FROM @source_key_list k LEFT JOIN dbo.dim_Customer c ON c.CustomerSourceKey = k.SourceKey WHERE sk_Customer IS NULL SELECT sk_Customer, CustomerSourceKey FROM dbo.dim_Customer c JOIN @source_key_list k ON k.SourceKey = c.CustomerSourceKey END GO

The TVP must be declared READONLY.  You cannot perform any DML (i.e. INSERT, UPDATE, DELETE) against the TVP;  you can only reference it in a SELECT statement.  The stored procedure joins the TVP to the customer dimension to determine any source keys that do not already exist then inserts them.  The stored procedure then joins the TVP to the customer dimension to return a result set of the source key and their corresponding surrogate key.

You can query sys.parameters to view any parameters that have been declared READONLY:

SELECT object_id, name FROM sys.parameters WHERE is_readonly = 1 GO

Declare TVP, Populate and Pass as a Parameter

You declare a T-SQL variable as a table type then populate it with rows by using an INSERT statement:

DECLARE @source_key_list SourceKeyList INSERT INTO @source_key_list SELECT 'CustomerID_001' UNION ALL SELECT 'CustomerID_002' UNION ALL SELECT 'CustomerID_003' EXEC dbo.stp_GetCustomerSK @source_key_list GO

For demonstration purposes the above SELECT statement just hard-codes values to insert for the source system keys; you would normally do a SELECT DISTINCT from your source system table to extract the list of source system keys on which you want to perform the surrogate key lookup.  The output from the above would look like this:

The output shows the surrogate key for each source key.