2

I have a sproc that has a couple inserts and updates. Only the amount of a specific update needs to be returned. I have tried returning or selecting @@ROWCOUNT but because of other selects in the sproc, it is buried and is difficult to access. Having NOCOUNT ON for the whole proc returns the counts for other inserts and updates as well, obviously. I'm wondering if I can do something like:

SET NOCOUNT ON
-- some inserts, selects, updates
SET NOCOUNT OFF
-- The update I care about
SET NOCOUNT ON

and receive just the count from that update? Thanks in advance.

Garrett Daniel DeMeyer
  • 931
  • 1
  • 13
  • 29

3 Answers3

2

Why yes, yes you can. That's exactly what SET NOCOUNT is for.

(Did you try this in MS SQL server management studio, or your test environment?)

DougM
  • 2,808
  • 17
  • 14
  • I just did and it works the way I'd want it to, that is it says "(1 row(s) affected)" instead of 4. If I ExecuteNonQuery in vb, do you think I'd see just 1 instead of 4 as a return value? – Garrett Daniel DeMeyer May 17 '13 at 15:17
  • I wouldn't think so, and looking at the [documentation for ExecuteNonQuery](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx) I don't see any reason that it would. The query runs in SQL server, and shouldn't behave differently for different accessing programs. – DougM May 18 '13 at 12:55
0

Yes you can. It should work no problem. Here is the MSDN documentation.

Matthew
  • 265
  • 4
  • 10
0

You can keep the result of the @@ROWCOUNT in variable it does not matter that other selects

-- some inserts, selects, updates
-- The update I care about
DECLARE @rowCount_updICareAbout INT
SET @rowCount_updICareAbout = @@ROWCOUNT
SELECT @rowCount_updICareAbout
Aleksandr Fedorenko
  • 16,594
  • 6
  • 37
  • 44