CODEWITHSUNDEEP
sqlsql-servert-sqlrowcount
user165502
user165502

Reputation: 41

Getting the ROWCOUNT value (not @@ROWCOUNT) in SQL

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?

Upvotes: 4

Views: 2812

Answers (3)

siggemannen
siggemannen

Reputation: 9159

I finally found the way of getting the ROWCOUNT setting, it's reported as part of DBCC USEROPTIONS output, which can be captured by using INSERT/EXEC.

Try to following script:

SET ROWCOUNT 5; -- For testing

CREATE TABLE #options (opt nvarchar(100), value nvarchar(1000))

INSERT INTO #options
EXEC    (N'DBCC USEROPTIONS')

SELECT  ISNULL((SELECT  TOP 1 value FROM #options WHERE opt = 'rowcount'), 0) -- returns 5

Upvotes: 1

gbn
gbn

Reputation: 432210

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

Upvotes: 2

SQLMenace
SQLMenace

Reputation: 134941

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

Upvotes: 1

Related Questions