Passing DataTable as table variable in SQL Server Ad-Hoc .NET Query

Question

I want to pass a table parameter to SQL Server 2008. Most of what I find on the Internet talks about doing this with a stored procedure, but is it possible to also do it with an Ad-Hoc query? With the code below, I get the error, "Column, parameter, or variable @idList. : Cannot find data type dbo.SiteType.". I think it might be executing the type definition as a totally separate query and 'forgetting' about it when the SELECT is initiated.

DataTable dt = new DataTable();
dt.Columns.Add("ID", Type.GetType("System.Guid"));

foreach (Guid guid in filter.FetchIDs)
{
    DataRow row = dt.NewRow();
    row["ID"] = guid;
}
sqlCmd.CommandText = "CREATE TYPE [dbo].[SiteType] AS TABLE(ID uniqueidentifier);" + "SELECT * FROM Site INNER JOIN @idList as FetchIDs on FetchIDs.ID=Site.ID;";
SqlParameter parameter = sqlCmd.Parameters.AddWithValue("@idList", dt);
parameter.TypeName = "[dbo].[SiteType]";

UPDATE: I added 'dbo.SiteType' as a type to the database, and now using this code it works, but returns zero rows:

DataTable dt = new DataTable();
dt.Columns.Add("ID", Type.GetType("System.Guid"));

foreach (Guid guid in filter.FetchIDs)
{
    DataRow row = dt.NewRow();
    row["ID"] = guid;
}
sqlCmd.CommandText = "SELECT * FROM Site INNER JOIN @idList as FetchIDs on FetchIDs.ID=Site.ID;";
SqlParameter parameter = sqlCmd.Parameters.Add("@idList", SqlDbType.Structured);
parameter.TypeName = "[dbo].[SiteType]";
parameter.Value = dt;

If you want this type why don't you create in your database? The way this is coded you would have to drop the type also or it would not be able to run a second time. Even better would be to just use a stored procedure. The way you have this your code does not have any separation from the data. Last but not least....don't use AddWithValue. It is not good for performance and can get datatypes wrong. http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ — Sean Lange, Sep 04 '15 at 13:38
After you have initialised the `SqlParameter` you need to tell ado that it is a TVP by adding the property `parameter.SqlDbType = SqlDbType.Structured;` — M.Ali, Sep 04 '15 at 13:52
Thanks for your response. I know from what I posted it seems like an sproc is preferred, but I have other reasons why it is desirable to be ad-hoc (other queries on this table dynamically construct a parameter list and I would like to only have to maintain the select field list in one place - my API layer). I was not aware that creating a custom type was permanent. I will try defining it in the database instead. — user1054922, Sep 04 '15 at 13:58

score 0 · Answer 1 · answered Sep 04 '15 at 14:37

Final, working code here (after creating the table data type in the server):

DataTable dt = new DataTable();
dt.Columns.Add("ID", Type.GetType("System.Guid"));

foreach (Guid guid in filter.FetchIDs)
{
    DataRow row = dt.NewRow();
    row["ID"] = guid;
    dt.Rows.Add(row);
}
sqlCmd.CommandText = "SELECT * FROM Site INNER JOIN @idList as FetchIDs on FetchIDs.ID=Site.ID;";
SqlParameter parameter = sqlCmd.Parameters.Add("@idList", SqlDbType.Structured);
parameter.TypeName = "[dbo].[SiteType]";
parameter.Value = dt;

Passing DataTable as table variable in SQL Server Ad-Hoc .NET Query

1 Answers1