Binding empty list or null value to table valued parameter on a stored procedure (.NET)

Question

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?

Answer 1

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.

Answer 2

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'.