How to get data from stored procedure when NO COUNT is on?

Question

0.00/5 (No votes)

See more:

Hi Everyone,

I was going through lot of articles and found that

SET NOCOUNT ON;

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

Posted 4-Jan-18 7:25am

Member 10977208

Updated 4-Jan-18 8:02am

RedDk

v2

Add a Solution

Comments

RedDk 4-Jan-18 13:46pm

NOCOUNT is only a message shim.

SSMSE generates a lot of code upon right-clicks, much of it contains just such a NOCOUNT statement. 'Probably the place where the authors of a "lot of articles" were infected by the notion that there was something special about it.

Ignore it.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2018-01-04T07:43:00

Take a look at MSDN documentation[^]:

Quote:
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.

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[^]