April 18, 2013 at 8:15 am
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?
April 18, 2013 at 9:12 am
Stephanie Giovannini (4/18/2013)
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?
That is kind of a nasty situation. Maybe you could create a method in C# to build the table based on the current definitions and then only populate the columns it cares about?
This will get you the list of columns.
select * from sys.columns c
join sys.table_types t on t.type_table_object_id = c.object_id
where t.name = 'YourTableTypeHere'
order by c.column_id
Maybe you could leverage this to build datatable in C# first and then have the code insert into only the columns it cares about.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2013 at 9:27 am
Interesting idea...
We use SqlDataRecord and SqlMetaData to populate the rows of the table type in C#. I suppose the C# could look up the columns and add any that aren't already defined in SqlMetaData as nullable columns... it doesn't solve our problem today, but it's definitely an option. Thanks.
April 18, 2013 at 10:12 am
Stephanie Giovannini (4/18/2013)
Interesting idea...We use SqlDataRecord and SqlMetaData to populate the rows of the table type in C#. I suppose the C# could look up the columns and add any that aren't already defined in SqlMetaData as nullable columns... it doesn't solve our problem today, but it's definitely an option. Thanks.
You could even use a query to fill an empty resultset.
declare @MyTVP as YourTVP
select * from @MyTVP
This would give you back a dataset with all the columns and datatypes already.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply