如何在NO COUNT打开时从存储过程中获取数据？ [英] How to get data from stored procedure when NO COUNT is on?

问题描述

大家好，



我浏览了大量文章，发现

Hi Everyone,

I was going through lot of articles and found that

SET NOCOUNT ON;

是当你使用存储过程时建议使用，但我主要使用存储过程进行CRUD操作，我的问题是如果我想使用存储过程检索一些数据，并使用VB.Net将其放在前端，因为没有计数是设置为on只会显示N行受影响的消息。如何通过保持SET NOCOUNT ON来获得我想要的数据。



也许很多人会发现这个问题非常基本但这只是有点混乱我我有。



谢谢



我尝试了什么： < br $>


试过临时表但没什么帮助

is recommended when you are working with stored procedures, but I mostly use stored procedure for CRUD operations, My Question is what if i want to retrieve some data using stored procedure, and put that in front end using VB.Net, as the no count is set to on it will only display the message that N rows affected. How can i get the data which i want by keeping the SET NOCOUNT ON.

Maybe many of you will find this question very basic but this is just a little confusion I am having.

Thank you

What I have tried:

Tried the temporary table but not much helpful

推荐答案

看看 MSDN文档 [ ^ ]：

Take a look at MSDN documentation[^]:

Quote：

停止显示受Transact-SQL语句影响的行数或存储的消息作为结果集的一部分返回的过程。

...

SET NOCOUNT ON阻止向客户端发送DONE_IN_PROC消息存储过程中的每个语句。对于包含多个不返回实际数据的语句的存储过程，或者对于包含Transact-SQL循环的过程， 将SET NOCOUNT设置为ON可以显着提高性能，因为网络流量大大减少 的。

Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.
...
SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

结论：强烈建议使用 SET NOCOUNT ON 。



关于这条指令有很多神话。我建议阅读：

SQL SERVER - SET NOCOUNT对@@ ROWCOUNT的影响 - 带有Pinal Dave的SQL权限 [ ^ ]

性能惊喜和假设：SET NOCOUNT ON - SQLPerformance.com [ ^ ]

Conclusion: Using SET NOCOUNT ON is strongly recommended.

There's tons of myth about this instruction. I'd suggest to read:
SQL SERVER - Effect of SET NOCOUNT on @@ROWCOUNT - SQL Authority with Pinal Dave[^]
Performance Surprises and Assumptions : SET NOCOUNT ON - SQLPerformance.com[^]

这篇关于如何在NO COUNT打开时从存储过程中获取数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！