CODEWITHSUNDEEP
sql-serversql-server-2008rowcount
BrunoLM
BrunoLM

Reputation: 100331

Is it safe to use SET ROWCOUNT?

I am using SET ROWCOUNT because the value comes from a parameter into my procedure.

SET ROWCOUNT @take 

SELECT * FROM Something

SET ROWCOUNT 0

Is it possible to another procedure executes at the same time and get the rowcount setting, or is it perfectly safe to use it on a stored procedure?

Upvotes: 7

Views: 18989

Answers (4)

Christian
Christian

Reputation: 36

Sorry for the Necro, just stumbled upon this on SQLServer 2016 and thought it might be interesting: If you are using a SELECT <x> INTO tabY from tabX, this will also be affected if you have SET ROWCOUNT <> 0, even if this select happens in a procedure that you are calling. So be careful.

Upvotes: 0

Garry T
Garry T

Reputation: 11

I am using it like this FOR SELECT ONLY SO ITS NOT going to be deprecated yet as a monitor thread i need to select all pending rows from this one table but, as the process thread i only want to process 10 rows at a time but i don't want spaghetti code in my sproc so here is my solution:

parameters ( @top int = 0 )

IF @TOP > 0
    SET ROWCOUNT @TOP -- only put a cap on the select if we pass in the @top value

-- select as normal select * from aTable -- either gets all rows or gets a virtual "top (@top)" -- example: exec up_myProc @top=10 -- gets top 10 -- exec up_myProc @top=0 -- gets all rows

Upvotes: 1

Ryan Wade
Ryan Wade

Reputation: 81

I am adding this answer for the benefit of people who may still search for this.

It is no longer safe to use SET ROWCOUNT as it will be deprecated in the next version of SQL Server:

TechNet: Deprecated Database Engine Features

Upvotes: 8

HLGEM
HLGEM

Reputation: 96572

Rowcount is specific to your current scope, so you are safe there. However, Books Online tells me this (which may or may not affect your needs):

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Do not use SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax. For more information, see DELETE (Transact-SQL), INSERT (Transact-SQL), or UPDATE (Transact-SQL).

TOP can use variables too and now can be used in INSERT,UPDATE and DELETE statments. (Hey I learned something new today.) Look up how to use TOP with variables in Books online.

Upvotes: 8

Related Questions