All geek questions in one place

Getting ROWCOUNT value (not @@ ROWCOUNT) in SQL

Is there any way to see that ROWCOUNT is installed?

The component that is used to call my stored procedure has the ability to limit the number of lines, but apparently does this by setting ROWCOUNT. One stored procedure returns only one aggregated row, but intermediate requests sometimes return more than the limit and are truncated. The function that performs all this is common and is used to call other stored procedures; some of my other procedures may need a limitation.

Now I set ROWCOUNT to a very large number at the top of my stored procedure, and then set it back to the (hard-coded) regular limit before I return my result. I do not support the component that calls my stored procedures, so I may not know if the limit of the returned string has changed. What I would like to do is set the local variable to the current ROWCOUNT value, and then set it at the end. Is there any way to see that ROWCOUNT is installed?

+4
source share
2 answers

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

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

You may be able to play around with this to find out if you can use it.

Now I set ROWCOUNT to a very large number at the top of the stored procedure

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

+3
source

Stop using SET ROWCOUNT . In any case, this will be partially obsolete.

Use the TOP that has been there since SQL Server 2000: 11 years. This is for each request and does not affect the intermediate lines, which means that you can use it if necessary, and not globally, as it is now.

Edit, February 2012

It is removed in the next release after SQL Server 2012, too, to insert, update, and delete

+2
source

Source: https://habr.com/ru/post/1332932/

More articles:


All Articles