Calling stored procedure with table value parameters returns no rows

Question

I am using entity framework 6, calling a stored procedure which takes a table value parameter. The stored procedure executes without error but returns no rows. I use profiler to trace the call so I can see how EF executes it. If I manually call the stored procedure a different way it will return rows as expected.

Here's my application code:

public IEnumerable<Table1> ListTableValueParameter(IEnumerable<int> lstIDs)
{
    //Convert enumerable int to DataTable
    System.Data.DataTable dtIDs = new System.Data.DataTable();
    dtIDs.Columns.Add("ID", typeof(int));

    foreach(int i in lstIDs)
    {
        dtIDs.Rows.Add(i);
    }

    var db = new POCDBContext();

    //Create parameter for table
    System.Data.SqlClient.SqlParameter p = new System.Data.SqlClient.SqlParameter();
    p.SqlDbType = System.Data.SqlDbType.Structured;
    p.ParameterName = "@IDS";
    p.Value = dtIDs;
    p.TypeName = "dbo.IntegerTableParameter";

    using (var connection = db.Database.Connection)
    {
        connection.Open();
        var command = connection.CreateCommand();
        command.CommandText = "EXEC [dbo].pList_TableProperties";

        command.Parameters.Add(p);

        //Execute stored procedure
        using (var reader = command.ExecuteReader())
        {
            return
                ((IObjectContextAdapter)db)
                    .ObjectContext
                    .Translate<Table1>(reader).ToList();

        }
    }   
}

This code executes the following sql statements at runtime, which returns no records:

declare @p3 dbo.IntegerTableParameter
insert into @p3 values(1)
insert into @p3 values(2)
insert into @p3 values(3)
insert into @p3 values(4)
insert into @p3 values(5)

exec sp_executesql N'EXEC [dbo].pList_TableProperties',N'@IDS [dbo].[IntegerTableParameter] READONLY',@IDS=@p3

If I run the following sql, it will return records:

declare @p3 dbo.IntegerTableParameter
insert into @p3 values(1)
insert into @p3 values(2)
insert into @p3 values(3)
insert into @p3 values(4)
insert into @p3 values(5)

EXEC [dbo].pList_TableProperties @p3

Is there something different I can do on the client side to get this to execute correctly?

`command.CommandText = "[dbo].pList_TableProperties"; command.CommandType = CommandType.StoredProcedure` shouldn't really fix your problem but is good to do regardless. — Jeroen Mostert, Aug 01 '17 at 15:37
Actually @JeroenMostert I think that will fix the problem. Since the command is not set to StoreProcedure and there is no mention of the parameter in the pass through query it would be added to the command but would behave like not passing a parameter at all. — Sean Lange, Aug 01 '17 at 15:46
@JeroenMostert: I think your solution is correct. Another option: `command.CommandText = "EXEC [dbo].pList_TableProperties @IDS";` instead of `command.CommandText = "EXEC [dbo].pList_TableProperties";` — TriV, Aug 01 '17 at 15:53
Oops. I completely overlooked that. I would have expected `sp_executesql` to complain about binding parameters not present in the statement. Anyway, that's why it's a good idea to specify `CommandType` when you're calling a stored procedure, because then you will definitely get errors about missing and superfluous parameters. — Jeroen Mostert, Aug 01 '17 at 15:53
Also, no need to set the parameter TypeName property when passing a TVP to a proc. — Dan Guzman, Aug 02 '17 at 00:41
@JeroenMostert and others are correct, the solution is to set the CommandType! You should specify that as an answer so I can accept. When I do that, I also can leave out the "Exec" in the command and you are also right about not having to set the TypeName! — Jeremy, Aug 02 '17 at 21:10

score 0 · Answer 1 · answered Aug 02 '17 at 07:44

This code executes the following sql statements at runtime, which returns no records:

declare @p3 dbo.IntegerTableParameter
insert into @p3 values(1)
insert into @p3 values(2)
insert into @p3 values(3)
insert into @p3 values(4)
insert into @p3 values(5)

exec sp_executesql N'EXEC [dbo].pList_TableProperties',N'@IDS [dbo].[IntegerTableParameter]

This code returns no record because it does not pass any parameter to stored procedure. This does not cause an error but outputs nothing. The code should look like this (I evidenced missing parameter with *****) :

exec sp_executesql N'EXEC [dbo].pList_TableProperties *****@IDS*****',N'@IDS [dbo].[IntegerTableParameter]

Calling stored procedure with table value parameters returns no rows

1 Answers1