Table Valued Parameters (TVPs) were introduced in SQL Server 2008. TVPs allow passing multiple rows of data to the server. It can be passed to a Stored Procedure as a parameter. It improves the performance of the application since it prevents several round trips to the server for saving the records in the database. Prior to TVPs we need to pass the data by delimiting with some characters and for doing that we need to write some logic for SQL Server for separating the data.
TVPs are stored in Tempdb and can be created and destroyed automatically behind the scenes.
We can have select or join against TVP.
It is similar to temp tables, table variables or CTEs.
It can be passed from client to server over a network.
Creating TVP in SQL Server
We will first create a table and then create a type of that. I have created a table named Books as in the following:
CREATE TABLE [dbo].[Books](
[BookId] [int] NOT NULL,
[BookName] [varchar](50) NOT NULL,
[AuthorName] [varchar](50) NOT NULL,
[Email] [varchar](80) NOT NULL,
[DateCreated] [datetime] NOT NULL,
CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED
(
[BookId] ASC
)
)
GO
Now we will create a TVP for the preceding table.
Create Type BooksUDT as Table
(
BookID int,
BookName varchar(50),
AuthorName varchar(50),
Email varchar(80)
)
I have not created a DateCreated column in the TVP since I want this column value to be the latest depending on my SQL Server date. Now I will create a Stored Procedure that will accept this table value parameter as parameter and save multiple rows into the database.
Create proc usp_SaveBooks
@books dbo.BooksUDT Readonly
as
Begin
insert into dbo.Books Select *,SYSDATETIME() from @books
End
At this point we have completed all the work at the SQL Server side. Now we will create a C# Console project and send the table value parameter from C# to SQL Server. Once the project has been created we will add a reference of Entity Framework and refer to our database. Now I will create a datatable that will represent our TVP and will add rows in that. Then we will create one parameter of type SqlParameter to which that datatable will be assigned.
var bookdt = new DataTable();
bookdt.Columns.Add("bookId", typeof(int));
bookdt.Columns.Add("BookName", typeof(string));
bookdt.Columns.Add("AuthorName", typeof(string));
bookdt.Columns.Add("Email", typeof(string));
bookdt.Rows.Add(1, "C#", " C# test", "Csharp@gmail.com");
bookdt.Rows.Add(2, "ASP.NET", " ASP.NETtest", "aspnet@gmail.com");
bookdt.Rows.Add(3, "SQl Server", " SQl Server test", "sql@gmail.com");
bookdt.Rows.Add(4, "OOPS", " OOPS test", "oops@gmail.com");
var parameter = new SqlParameter("@books", SqlDbType.Structured);
parameter.Value = bookdt;
parameter.TypeName = "dbo.BooksUDT";
Then we will call the Stored Procedure by passing that SQL parameter to that.
using (SampleDbEntities db = new SampleDbEntities())
{
db.Database.ExecuteSqlCommand("exec dbo.usp_SaveBooks @books", parameter);
}
Disadvantages of Table Value Parameter
TVPs are read-only; once created they cannot get changed
The Output keyword cannot be used with them
To change the schema of a TVP we need to recreate it
Statistics are not maintained on TVPs
