Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is there a TVP equivalent to an optional parameter?


Is there a TVP equivalent to an optional parameter?

Author
Message
Stephanie Giovannini
Stephanie Giovannini
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1198 Visits: 1508
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?
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16680 Visits: 17038
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)
Stephanie Giovannini
Stephanie Giovannini
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1198 Visits: 1508
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.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16680 Visits: 17038
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search