Add DataTable as parameter in SQL

Question

I need to add DataTable to SQL query.

queryText = @"
WITH TaskDataTable AS
(
    SELECT *
    FROM @dataTable
)
INSERT INTO {0}.dbo.Tasks_Projects_PIR
...
FROM
    TaskDataTable as TaskDataTable
    LEFT JOIN dbo.MSP_EpmTask_UserView as t1 
    ON 1 = 1";


using (SqlCommand sqlCommand = new SqlCommand(queryText, ConfigurationProvider.sqlConnection))
{
    sqlCommand.CommandType = CommandType.Text;

    sqlCommand.Parameters.AddWithValue("eventUID", eventUid);                
    SqlParameter parameter = sqlCommand.Parameters.AddWithValue("@dataTable", dataTable);
    parameter.TypeName = "dbo.Tasks_Projects_PIR";
    parameter.SqlDbType = SqlDbType.Structured;

    sqlCommand.ExecuteNonQuery();
}

DataTable creation code:

DataTable myTvpTable = new DataTable("taskDataTable");
myTvpTable.Columns.Add("TaskUid", typeof(string));
myTvpTable.Columns.Add("TaskPredecessors", typeof(string));
myTvpTable.Columns.Add("TaskSuccessors", typeof(string));

foreach (ProjectDataSet.TaskRow taskRow in DataSetMaster.projectDS.Task)
{
    var predecessors = string.Empty;
    var successors = string.Empty;
    PSIWorker.GetPredecessorsAndSuccessors(taskRow, out predecessors, out successors);

    DataRow row = myTvpTable.NewRow();
    row["TaskUid"] = taskRow.TASK_UID.ToString();                
    row["TaskPredecessors"] = predecessors;
    row["TaskSuccessors"] = successors;
    myTvpTable.Rows.Add(row);
}

I have next errors:

Column, parameter, or variable @dataTable. : Cannot find data type* dbo.Tasks_Projects_PIR.

Or, if I don't set parameter.TypeName:

The table type parameter 'dataTable' must have a valid type name.

Can I use DataTable as parameter?

Use the `DataTable.Load` method to fill your table with values from the `SqlDataReader` — styx, Oct 03 '17 at 06:47
Should i have something object = parameter.TypeName in database? What object it should be? — DeRibura, Oct 03 '17 at 06:56
Can't you just wrap the TVP query as stored procedure? I think using TVP is easier inside a stored procedure, you can declare table type & pass `@datatable` parameter on it. — Tetsuya Yamamoto, Oct 03 '17 at 06:57
I don't want to use Stored procedure, because it will be use only once — DeRibura, Oct 03 '17 at 07:00
@DeRibura my mistake you can try and look [here](https://stackoverflow.com/questions/9075159/how-to-insert-a-data-table-into-sql-server-database-table) — styx, Oct 03 '17 at 07:16
I don't think table valued parameters works with inline sql. AFAIK, they only work with stored procedures (and user defined functions). — Zohar Peled, Oct 03 '17 at 07:26
@ZoharPeled: [The documentation](https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-ver15) says something different: "You can use table-valued parameters to send multiple rows of data **to a Transact-SQL statement** or a routine" ... "A table-valued parameter is scoped to the stored procedure, function, **or dynamic Transact-SQL text**" — Ben Voigt, Mar 28 '22 at 21:19

score 1 · Answer 1 · edited May 23 '18 at 17:09

You need to create a table type in your database:

CREATE TYPE [dbo].[TaskDataTable] AS TABLE
(
    TaskUid varchar(20),
    TaskPredecessors varchar(20)
)

Use the above table type in a Stored Procedure:

create procedure [dbo].spEmptask
(
    @taskTable TaskDataTable readonly
) 
As
BEGIN
    --your logic
END

C# Code:

using(SqlConnection con = new SqlConnection(connectionstring))
{
    SqlCommand command = new SqlCommand("spEmptask",con);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(new SqlParameter("@taskTable", dt(your DataTable)));
}

Add DataTable as parameter in SQL

1 Answers1

Linked