Hello:

Is there any way to get the SET ROWCOUNT value?

No, I do not mean @@ROWCOUNT. Due to this name collision, I have
not found an answer in my searching. What would replace the blanks in
the code below if I want to see the results 10 then 0?

set rowcount 10
_____
set rowcount 0
_____

Generalise to any SET parameter values.

Pointers welcome.

Sincerely,

Gene Wirchenko

On 2010-12-24 03:35, Gene Wirchenko wrote:

DECLARE @c INT;
SET ROWCOUNT 10;
SELECT @c = n FROM Nums ORDER BY n;
SET ROWCOUNT 0;
SELECT @c + 1;

I assume you've got a "nums" table, of course (mine contains zero, hence the
+1).

then take a peek:

SELECT * FROM sys.dm_exec_sessions WHERE session_id = @@SPID;

Beware, the table has a row_count column but this does *not* correspond to
the ROWCOUNT setting (I'm not exactly sure what it *is* supposed to contain,
despite BOL describing it as "Number of rows returned on the session up to
this point").

--
J.

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
I can't really think of a good way. You could to "SELECT 1 INTO #temp FROM
bigtable" and then check @@rowcount, but it's not good for performance when
SET ROWCOUNT is 0.

In any case, I recommend against any use of SET ROWCOUNT. This is a very
old command, which predates TOP and cursors. I see little reason to use it
today, and if it was up to me, it would be taken out of the product.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx