Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is there a TVP equivalent to an optional parameter? Expand / Collapse
Author
Message
Posted Thursday, April 18, 2013 8:15 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 24, 2014 5:08 AM
Points: 424, Visits: 395
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?
Post #1443893
Posted Thursday, April 18, 2013 9:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:04 PM
Points: 13,095, Visits: 11,929
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1443931
Posted Thursday, April 18, 2013 9:27 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 24, 2014 5:08 AM
Points: 424, Visits: 395
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.
Post #1443945
Posted Thursday, April 18, 2013 10:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:04 PM
Points: 13,095, Visits: 11,929
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1443981
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse