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 Students as in the following:
- CREATE TABLE [dbo].[Students](
- [StudentId] [int] NOT NULL,
- [FirstName] [varchar](50) NOT NULL,
- [LastName] [varchar](50) NOT NULL,
- [Email] [varchar](80) NOT NULL,
- [DateCreated] [datetime] NOT NULL,
- CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED
- (
- [StudentId] ASC
- )
- )
- GO
Now we will create a TVP for the preceding table.
- Create Type StudentsUDT as Table
- (
- StudentID int,
- FirstName varchar(50),
- LastName 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_SaveStudents
- @students dbo.StudentsUDT Readonly
- as
- Begin
- insert into dbo.Students Select *,SYSDATETIME() from @students
- 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 studentdt = new DataTable();
- studentdt.Columns.Add("StudentId", typeof(int));
- studentdt.Columns.Add("FirstName", typeof(string));
- studentdt.Columns.Add("LastName", typeof(string));
- studentdt.Columns.Add("Email", typeof(string));
- studentdt.Rows.Add(1, "Test1", "test", "[email protected]");
- studentdt.Rows.Add(2, "Test2", "test", "[email protected]");
- studentdt.Rows.Add(3, "Test3", "test", "[email protected]");
- studentdt.Rows.Add(4, "Test4", "test", "[email protected]");
-
- var parameter = new SqlParameter("@students", SqlDbType.Structured);
- parameter.Value = studentdt;
- parameter.TypeName = "dbo.StudentsUDT";
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_SaveStudents @students", 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
vaibhav guptaPosted Oct 1, 2020, 7:37 PM
Thanks buddy again for helping , Its Vaibhav from India :)
David ConditPosted Jan 25, 2018, 5:49 PM
Thanks, Ritesh! This worked great for me.
PRAMOD DAS M.D. 1125936Posted May 13, 2015, 4:45 AM
I tried to add the procedure and bumped into the same message from VS2012. Ritesh, could u please tell which is the version of EF you are using. Thanks!
Priya DharshiniPosted Apr 23, 2015, 9:14 AM
Hey this worked in visual studio 2013 very well. But can this design work in VS2012 as well???
Gowtham RajamanickamPosted Apr 1, 2015, 1:07 AM
good show
srinivas RapakaPosted Nov 12, 2014, 5:03 AM
simply super . nice one
Guest UserPosted Aug 1, 2014, 10:46 PM
+1
Ravi ShekharPosted Jul 27, 2014, 7:18 AM
good one