I have a BizTalk orchestration which is sending a message that is mapped to a stored proc using a generated schema with the WCF-SQL adapter. One of the parameters to the stored proc is a table value parameter. This is working fine when there are records that BT can translate into the table value. However, if there are a zero records, I still need the proc to be fired off, because there are other parameters supplied that can still be acted upon. But the orchestration crashes with
System.NotSupportedException: DBNull value for parameter '@DetailRecords' is not supported. Table-valued parameters cannot be DBNull.
Apparently you can get around this error and execute the stored proc without supplying that parameter, and SQL Server will generate an empty table automatically.
Is there any way to get BizTalk to fire off a stored proc that contains a table value parameter, without trying to pass the parameter as DBNull?