4

Is there a way to see what ROWCOUNT is set to?

The component that is used to call my stored procedure has an option to limit how many rows are returned, but it apparently does that by setting ROWCOUNT. One stored procedure only returns a single aggregated row, but the intermediate queries sometimes return more than the limit and are getting truncated. The function that does all of this is generic and is used to call other stored procedures; some my other procedures may need the limit.

Right now I am setting ROWCOUNT to a very large number at the top of my stored procedure and then setting it back to the (hard-coded) regular limit before I return my result. I don't maintain the component that calls my stored procedures so I may not know if the returned row limit is changed. What I'd like to do is set a local variable to the current ROWCOUNT value, and then set it back at the end. Is there any way to actually see what ROWCOUNT is set to?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
user165502
  • 41
  • 1
  • 2

2 Answers2

3

If you query the sys.dm_exec_sessions dmv it will return the number of rows returned on the session up to this point (should of course be the same as @@rowcount). 

SELECT row_count FROM sys.dm_exec_sessions
WHERE session_id = @@spid

You might be able to play around with that to see if you can use it

Right now I am setting ROWCOUNT to a very large number at the top of my stored procedure

You can also just do SET ROWCOUNT 0, in this case it will return all rows

SQLMenace
  • 132,095
  • 25
  • 206
  • 225
  • 1
    Good to know about SET ROWCOUNT 0, I wasn't sure what the default was. What I want to be able to do is say SET ROWCOUNT 500 and then do something like SELECT ROWCOUNT FROM sys.dm_exec_sessions and get the number 500 back. – user165502 Dec 21 '10 at 17:57
2

Stop using SET ROWCOUNT. It's being partially deprecated anyway.

Use TOP which has been there since SQL Server 2000: 11 years. This is per query and does not affect intermediate rows which means you can appyl it when needed, not globally as you are now.

Edit, Feb 2012

It's being removed in the next release after SQL Server 2012 too for insert, update and delete

gbn
  • 422,506
  • 82
  • 585
  • 676
  • Probably yes, but I am using Delphi 7 and it appears that this is how it enforces the MaxRecords value on ADO queries. – user165502 Dec 21 '10 at 19:56