Menu
How to pass table valued parameter to stored procedure using ADO.NET?
382
Table-valued parameters are declared by using user-defined table types. 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.
Table-Valued Parameters aka TVPs are commonly used to pass a table as a parameter into stored procedures or functions. They are helpful in a way, we can use a table as an input to these routines and we can get rid of dealing more complex steps to achieve this process.
Create a user-defined table type that corresponds to the table that you want to populate. Pass the user-defined table to the stored procedure as a parameter. Inside the stored procedure, select the data from the passed parameter and insert it into the table that you want to populate.
Create type in SQL Server:
CREATE TYPE [dbo].[MyDataType] As Table ( ID INT, Name NVARCHAR(50) ) Create Procedure:
CREATE PROCEDURE [dbo].[MyProcedure] ( @myData As [dbo].[MyDataType] Readonly ) AS BEGIN SELECT * FROM @myData END Create DataTable in C#:
DataTable myDataTable = new DataTable("MyDataType"); myDataTable.Columns.Add("Name", typeof(string)); myDataTable.Columns.Add("Id", typeof(Int32)); myDataTable.Rows.Add("XYZ", 1); myDataTable.Rows.Add("ABC", 2); Create SQL Parameter:
SqlParameter parameter = new SqlParameter(); parameter.ParameterName = "@myData"; parameter.SqlDbType = System.Data.SqlDbType.Structured; parameter.Value = myDataTable; command.Parameters.Add(parameter); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
