Why are table valued parameters to SQL Server stored procedures required to be input READONLY?

Question

Can anyone explain the design decision behind preventing table valued parameters from being specified as output parameters to stored procedures?

I can't count the number of times I've started building out a data model hoping to completely lock down my tables to external access (you know...implementation details), grant applications access to the database through stored procedures only (you know... the data interface) and communicate back and forth with TVPs only to have SSMS call me naughty for having the audacity to think that I can use a user-defined table type as the transfer object between my data service and my application.

So someone please provide me a good reason why TVPs were designed to be readonly input parameters.

Answer 1

In the presentation on Optimizing Microsoft SQL Server 2008 Applications Using Table Valued Parameters, XML, and MERGE by Michael Rys he says. (at 32:52)

Note that in SQL Server 2008 table valued parameters are read only. But as you notice we actually require you to write READONLY. So that actually then means that at some point in the future maybe if you say please, please please often enough we might be able to actually make them writable as well at some point. But at the moment they are read only.

Here is the connect item you should use to add your "please". Relax restriction that table parameters must be readonly when SPs call each other.

Srini Acharya made a comment on the connect item.

Allowing table valued parameters to be read/write involves quite a bit of work on the SQL Engine side as well as client protocols. Due to time/resource constraints as well as other priorirites, we will not be able to take up this work as part of SQL Server 2008 release. However, we have investigated this issue and have this firmly in our radar to address as part of the next release of SQL Server.