SQL Server Table Valued Parameter

Hello Folks,

SQL Server Table Valued Parameter is a new parameter type which has been introduced with the arrival of SQL Server 2008.

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

Steps for Creating Table-Valued Parameters:

Following are the steps as well as with the appropriate code-

Step 1: Create a table type and define the table structure.

USE School
CREATE TYPE Piyush_TableType AS TABLE
(
[SID] INT, 
Name VARCHAR(50),
City VARCHAR(20),
[State] VARCHAR(30)
)

Step 2: Create a Stored Procedure which has a parameter of the table type.

CREATE PROCEDURE Person_Insert
(
@MyTable Piyush_TableType READONLY
)
AS
INSERT INTO Person(Person_ID, Name, City, [State])
SELECT [SID],Name, City, [State]
FROM @MyTable;

Step 3: Now, declare a variable of the table type, and reference the table type.

DECLARE @Students Piyush_TableType

Step 4: Fill the table variable by using an INSERT statement.

INSERT INTO @Students VALUES (1, 'Piyush', 'Bilaspur', 'Chhatissgarh')
INSERT INTO @Students VALUES (2, 'Prashant', 'Bhilai', 'Chattissgarh')
INSERT INTO @Students VALUES (3, 'Harsh', 'Kolkata', 'West Bengal')
INSERT INTO @Students VALUES (4, 'Ankit', 'Dhanbaad', 'Jharkhand')

Step 5: Now it’s time to EXECUTE the stored procedure.

EXEC Person_Insert @Students

If you want to check whether the data is being inserted or not, use the SELECT query:

SELECT * FROM Person

The result is:

1_SQL_Server_New_Table_Value_Parameter

Benefits of Using Table-Valued Parameters:

  1. It enables us to include complex business logic in a single routine.
  2. It does not acquire any kind of locks for the initial population of data from a client.
  3. It provides a simple programming model.
  4. It also reduces the round trips to the server.

Drawbacks of Using Table-Valued Parameters:

  1. SQL Server is not been able to maintain the statistics on columns of table valued parameters.
  2. We are not able to perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine. Therefore, Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines.
  3. We 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.

Well this was a small description about the Table Valued Parameters.

And also comments on this!!

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

2 Comments on “SQL Server Table Valued Parameter”

  1. Piyush,

    Thanks for the nice post.Can you please more on the benefit ‘It enables us to include complex business logic in a single routine.’ I am not really sure if this is a useful feature to use or not?

    Also, if you can tell which situations this feature is best suited for?

Leave a Reply

Your email address will not be published. Required fields are marked *