I'm trying to help my client solve an issue with frequent "breaking changes" to procedures that use table-valued-parameters.
The calling code is in a highly branched environment, but due to resource constraints, there is only one SQL Server database environment for all the development branches and the production-equivalent testing environment.
The resulting issues have been dealt with largely through the use of optional parameters. The calling code doesn't need to know about optional parameters that it doesn't use.
Recently, one of the business lines implemented a number of procedures using TVPs. This particular product is unique in the business for processing large batches, and it saw significant performance gains from the TVPs. Usage of TVPs is new for this client.
But now we are dealing with the problem of needing "optional" columns on the table definition. Different development branches need different optional columns, and they are headed out to production at different times. But there's no such thing as an optional column defintion. The calling code (C#) has to know about the new column, even if it's nullable.
Is there a technical solution to this problem? Anything that I'm missing?