Passing DataTable to stored procedure as an argument

Question

I have a data table created in C#.

DataTable dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Age", typeof(int));

dt.Rows.Add("James", 23);
dt.Rows.Add("Smith", 40);
dt.Rows.Add("Paul", 20);

I want to pass this to the following stored procedure.

CREATE PROCEDURE  SomeName(@data DATATABLE)
AS
BEGIN
    INSERT INTO SOMETABLE(Column2,Column3)
    VALUES(......);
END

My question is : How do we insert those 3 tuples to the SQL table ? do we need to access the column values with the dot operator ? or is there any other way of doing this?

StuartLC · Answer 1 · 2019-03-08T06:55:03.540

19

You can change the stored procedure to accept a table valued parameter as an input. First however, you will need to create a user defined table TYPE which matches the structure of the C# DataTable:

CREATE TYPE dbo.PersonType AS TABLE
(
    Name NVARCHAR(50), -- match the length of SomeTable.Column1
    Age INT
);

Adjust your SPROC:

CREATE PROCEDURE dbo.InsertPerson
    @Person dbo.PersonType READONLY
AS
BEGIN
  INSERT INTO SomeTable(Column1, Column2) 
     SELECT p.Name, p.Age
     FROM @Person p;
END

In C#, when you bind the datatable to the PROC parameter, you need to specify the parameter as:

parameter.SqlDbType = SqlDbType.Structured;
parameter.TypeName = "dbo.PersonType";

See also the example here Passing a Table-Valued Parameter to a Stored Procedure

edited Mar 08 '19 at 06:55

answered Apr 08 '15 at 06:46

StuartLC

100,561
17
199
269

Thank you for your answer. I am referring to MS SQL Server 2012. This type creation in it is not possible. Can you provide an alternative pls ? – nidarshani fernando Apr 08 '15 at 06:54
1

TVPs were made available in [SQL 2008 already](https://technet.microsoft.com/en-us/library/bb522526(v=sql.105).aspx) – StuartLC Apr 08 '15 at 06:58
when I tried creating the Type, it generates the error saying "syntax error near AS". can't figure out why it is happening – nidarshani fernando Apr 08 '15 at 07:09
Can you do a quick `SELECT @@VERSION`. I'm guessing you are <= 2005. – StuartLC Apr 08 '15 at 07:13
That's sad. If you can't upgrade, google for techniques of using Xml to pass collections and graphs down to stored procedures. You'll then serialize the DataSet to Xml in your data access layer, and then parse the @Xml in your proc. It really is messy, however. – StuartLC Apr 08 '15 at 07:29

score 2 · Answer 2 · answered Apr 08 '15 at 06:53

First you need to create a Userdefined type of table that resembles your actual table. See the example below,

CREATE TYPE SomeType AS TABLE 
(
   C1 int, 
   C2 VARCHAR(50)
)
After this you need to create a stored procedure that takes this table type   as parameter.


CREATE PROCEDURE SomeUSP
  @tabType SomeType READONLY
AS
BEGIN
   SET NOCOUNT ON;

   INSERT INTO Yourtable(C1,C2)
   SELECT C1,C2 FROM @tabType 
END

That's it...Job done :)

Passing DataTable to stored procedure as an argument

2 Answers2

Linked