Dapper: inserting rows into table variables

c# dapper insert sql table-variable

Question

i have lately been using dapper and all in all had no problems with it except when using table variables.

For demonstration, I use a modified example from this thread.

This code works without problems:

int tally = connection.Execute(
    "create table #t(Name nvarchar(max), Age int)\n" +
    "insert #t (Name,Age) values(@Name, @Age)", new[]
    {
        new {Age = 1, Name = "sam"},
        new {Age = 2, Name = "bob"}
    });

But this doesn't:

int tally = connection.Execute(
    "create table @t(Name nvarchar(max), Age int)\n" +
    "insert @t (Name,Age) values(@Name, @Age)", new[]
    {
        new {Age = 1, Name = "sam"},
        new {Age = 2, Name = "bob"}
    });

The only change is the use of table variables instead temporary tables (@ instead #). Dapper (or anything other in the chain?) seems to somehow mix this up with parameters and returns "Must declare the table variable @t".

Is there anything wrong in my usage or is this a bug/missing feature in dapper?

Best regards, Kc


UPDATE:

Just to clarify: @t is not a parameter to be set by dapper. @t is declared as a local table that's only existent for the currently running query. In my opinion, dapper should not distinguish between any type of table, be it a "normal" one, local/global temporary table or table variable.

A little bit more background information:

What I really want to do is:

  • Insert a list of Ids into an indexed table variable (or maybe temporary table if table variables don't work)
  • JOIN this table against one of my persistent tables and return the result.

My intention is to defeat a performance issue when SELECTing from my tables with an WHERE IN (...) clause

Popular Answer

If you're using SQL Server, then the syntax is incorrect. In SQL Server, table variables are not created the same way as regular tables. It should be like this:

DECLARE @t TABLE (
   -- table definition
);


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why