为什么需要以只读方式输入 SQL Server 存储过程的表值参数? [英] Why are table valued parameters to SQL Server stored procedures required to be input READONLY?

问题描述

谁能解释防止表值参数被指定为存储过程的输出参数背后的设计决策?

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

我不知道有多少次我开始构建数据模型，希望将我的表完全锁定为外部访问(你知道...实现细节)，通过存储过程授予应用程序访问数据库的权限仅(您知道...数据接口)并与 TVP 来回通信，只是让 SSMS 称我为顽皮，因为我大胆地认为我可以使用用户定义的表类型作为我的数据服务和我的申请.

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.

所以有人请给我一个很好的理由，为什么 TVP 被设计为只读输入参数.

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

推荐答案

在关于 优化 Microsoft SQL 的演示文稿中Server 2008 Applications Using Table Valued Parameters, XML, and MERGE by Michael Rys 他说.(在 32:52)

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)

请注意，在 SQL Server 2008 中，表值参数是只读的.但正如您所注意到的，我们实际上要求您编写 READONLY.以便实际上那么意味着在未来的某个时候也许如果你说拜托，拜托，请足够频繁，我们也许能够真正做到它们在某些时候也是可写的.但目前他们正在阅读仅.

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.

这是您应该用来添加请"的连接项.放宽对表参数必须在 SP 相互调用时只读的限制.

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 对连接项发表了评论.

Srini Acharya made a comment on the connect item.

允许读/写表值参数涉及很多SQL 引擎端以及客户端协议的工作.由于时间/资源限制以及其他优先事项，我们不会能够将这项工作作为 SQL Server 2008 版本的一部分.然而，我们已经调查了这个问题，并牢牢记住了这一点地址作为下一版本 SQL Server 的一部分.

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.

这篇关于为什么需要以只读方式输入 SQL Server 存储过程的表值参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！