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 sp 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 to an IEnumerable, and binding that to the table valued parameter for the sp. I have tried to bind an empty List, 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 sp declaration. What is the correct method for binding an empty list to at table valued parameter?

Answer 1

I believe you can't just take a List<T> (or List<int> in this case) and return it as an IEnumerable. You need to create an object that inherits from List<T> and implements IEnumerable<SqlDataRecord>. Then you can define the following:

IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
{
    // code to create and yield an SqlDataRecord object
    // The SqlDataRecord object should have a schema that matches 
    // the user-defined table type that the table-valued pair is declared as
}

When ExecuteNonQuery is called to run a stored procedure with a SqlDbType.Structured parameter (table-valued parameter), the collection passed for the parameter value is expected to implement IEnumerable<SqlDataRecord> so that IEnumerable<SqlDataRecord>.GetEnumerator can be called internally to fetch each new record to be sent over to the server.

I'm not sure if this gets by the empty enumeration, but I believe it is the way you are supposed to create a collection for a TVP.