68

I have created a stored procedure that takes a table valued parameter that is a table with a single column of type int. The idea is to simply pass a list of ids into the store procedure and allow the stored procedure to work with the data. However, in the case where there is no data to pass in, I am encountering problems (things work correctly when I have data). I am converting a List<int> to an IEnumerable<SqlDataRecord>, and binding that to the table valued parameter for the stored procedure. I have tried to bind an empty List<SqlDataRecord>, which resulted in the error:

System.ArgumentException: There are no records in the SqlDataRecord enumeration. To send a table-valued parameter with no rows, use a null reference for the value instead.

I then tried to bind a null value (which I thought was what the above message was getting at), but that only resulted in a different error message

System.NotSupportedException: DBNull value for parameter '@MainItemIdList' is not supported. Table-valued parameters cannot be DBNull.

It does not appear that you can declare the table valued parameter as nullable in the stored procedure declaration. What is the correct method for binding an empty list to at table valued parameter?

SharpC
  • 6,974
  • 4
  • 45
  • 40
Brian Jones
  • 701
  • 1
  • 6
  • 6

5 Answers5

96

The trick is: don’t pass in the parameter at all. The default value for a table-valued parameter is an empty table.

It is a shame that the exception message is so unhelpful.

Daniel Cassidy
  • 24,676
  • 5
  • 41
  • 54
  • 8
    It never hurts to add a new answer or improve one. The information is still relevant and someone else will find it useful (that's the whole point of this site!). +1 – Jason Down Jul 19 '11 at 18:58
  • Any idea how to do this if using plain old table adapters? TableAdapter templates a CLR proxy function with the same signature as the SP, and it expects a not-null value. Passing null or DbNull.Value throws an error. – Boris B. Oct 25 '11 at 14:26
  • Up 1. This is exactly what I needed to do - tested and it works great. – Jim Evans Oct 25 '11 at 18:17
  • +1 big thanks - confirmed this works for the `SqlDataRecord` / `SqlMetaData` approach to TVPs as well. – StuartLC Feb 19 '14 at 12:33
  • So, how would I change the code to not pass a parameter? In my case I have 5 TVPs parameters and each may be empty. I'm using code like this one BenefitsItemsLinkCollection itemsCollection = new BenefitsItemsLinkCollection(); itemsCollection.AddRange(items); itemsPar.Value = itemsCollection; itemsPar.TypeName = "siriusType_BenefitLinkedItems"; What should I change here for empty array of items? – Naomi Jun 23 '17 at 23:50
  • Agreed on the exception message. There is little chance I would have even tried this fix without your help! – Doug Aug 21 '18 at 12:41
7

I was a bit confused by what the 'not passing the parameter' statement means. What ends up working for Entity Framework ExecuteSqlCommandAsync() is this:

  new SqlParameter("yourParameterName", SqlDbType.Structured)
  {
      Direction = ParameterDirection.Input,
      TypeName = "yourUdtType",
      Value = null
  };

This will pass the parameter as 'default'.

Norbert Huurnink
  • 1,326
  • 10
  • 18
  • Not sure why it works for you, but this does not work at all for me. I get the exact error messages in the original question when using this approach. – Doug Aug 21 '18 at 12:33
4

Not passing a value does work but not in the case where I had multiple table value parameters to pass into the procedure. How I solved it was to specify a value of DEFAULT in my query string. For example,

string sqlQuery = "[dbo].[GetOrderData] @QueueId";

if (OrderIdList.Any())
{
    sqlQuery = sqlQuery + ", @OrderIdList";
}
else
{
    sqlQuery = sqlQuery + ", DEFAULT";
}

if (RegionIdList.Any())
{
    sqlQuery = sqlQuery + ", @RegionIdList";
}
else
{
    sqlQuery = sqlQuery + ", DEFAULT";
}

Kudos to http://www.sommarskog.se/arrays-in-sql-2008.html#Invoking where I found the solution for this.

Anthony
  • 81
  • 3
0

I get the error when passing an empty IEnumerable<int> but it works fine when I pass an empty List<int> instead.

jausel
  • 594
  • 7
  • 18
  • 1
    This didn't work for me. I get the same error whether passing an IEnumerable or List. – Keith Nov 27 '19 at 21:19
0

The confusion comes because when the error message states, "use a null reference for the value instead", it means C# null, not DBNull.Value. Setting a parameter to C# null passes the default value for the parameter, which in the case of a table-valued parameter is an empty table. Setting the value to DBNull.Value passes as SQL NULL value, which is not valid for a table-valued parameter.

Scott
  • 4,458
  • 1
  • 19
  • 27