如何防止存储过程返回中间记录集？
问题上下文：我使用 VisualStudio 2008 和类型化数据集，它提供了对 executescalar() 的“轻松”访问 执行标量返回第一个记录第一行的第一个字段的值（最后一部分经常被省略）
这种行为的问题是，大多数情况下您想要在计算最终 select @returnValue 语句返回的值之前发出一些其他请求。
所以我的问题是，一般来说，如何防止 SP 返回中间记录集？ （我尝试了 SET FMTONLY ON/OFF 但有不需要的副作用）
另一个相关问题是：如何防止 T-SQL UPDATE 语句 返回更新的行？有时您使用 UPDATE 来简单地更新...
谢谢！
示例：
BEGIN
SET NOCOUNT ON;
declare @c int
select @c=(select count(*) from work where ...)
select @c -- so ExecuteScalar() works
update sousblocs set myfield = @c
where ...
select @c --after the update, won't be seen by ExecuteScalar()
return @c --useless for ExecuteScalar
END
这是我的问题，可以通过第一个“select @c”来解决；但我仍然想知道你是否可以禁用重新设置输出....
Problem context : I use VisualStudio 2008 and typed datasets, which offer an "easy" access to executescalar()
Execute scalar returns the value of the first field of the first row of the first recorset (this last part being often omitted)
The problem with this behaviour is that most often you want to make some other requests before you compute the value to be returned with a final select @returnValue statement.
So my question is, in general, How do you prevent a SP to return intermediate recordsets?
(I tried SET FMTONLY ON/OFF but is has unwanted side behaviour)
Another related question is : how do you prevent a T-SQL UPDATE statement from returning the updated rows ? Sometimes you use UPDATE to simply UPDATE ...
thanks!
sample:
BEGIN
SET NOCOUNT ON;
declare @c int
select @c=(select count(*) from work where ...)
select @c -- so ExecuteScalar() works
update sousblocs set myfield = @c
where ...
select @c --after the update, won't be seen by ExecuteScalar()
return @c --useless for ExecuteScalar
END
this was my problem, and it can be solved with the first "select @c"; but I nevertheless wonder if you can disable recorset output....
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
1 - 在没有目标的情况下，存储过程中不要有多个选择语句。即，如果您运行
SELECT 1,2,3，然后运行SELECT 4,5,6，它将返回 2 个记录集。2 - 我不确定为什么您在运行更新时会收到记录。尝试设置
SET NOCOUNT ON以关闭“xx rows受影响”消息。1 - Don't have multiple select statements in your stored proc without a target. I.e. if you run
SELECT 1,2,3and thenSELECT 4,5,6it will return 2 recordsets.2 - I'm not sure why you are getting records back when you run an update. Try setting
SET NOCOUNT ONto turn off the "xx rows affected" messages.