In the BOL for SET ROWCOUNT, it says:

"It is recommended that DELETE, INSERT, and UPDATE statements currently
using SET ROWCOUNT be rewritten to use the TOP syntax."

However, TOP Doesn't see to work with a variable.

For example,

SELECT TOP @MyNum * FROM MyTable

Is it ok to use ROWCOUNT if you have a dynamic # of rows?

In SQL Server 2005, you will be able to use a variable (or any expression)
if you put it in parentheses:

SELECT TOP (@MyNum) * FROM MyTable ORDER BY someColumn

For now, dynamic solutions to this require either ROWCOUNT or
clumsier techniques. Assuming you want the TOP @MyNum-many
rows in order of someColumn ASC, and someColumn is unique, these
are possibilities [untested, and the second may need a bit of
modification if there is an identity column].

select * from MyTable
where (
select count(*)
from MyTable as T
where T.someColumn < MyTable.someColumn
) <= @MyNum


-- another possibility
select top 1 * into #
from MyTable
where 1 = 0

while @MyNum > 0 begin
set @MyNum = @MyNum - 1
insert into #
select top 1 *
from MyTable
where someColumn not in (
select someColumn
from #
)
order by someColumn
end

Steve Kass
Drew University

Mike W wrote:

Yes, SET ROWCOUNT will work just fine.

Just don't forget to 'reset' it with SET ROWCOUNT 0 after the
delete/insert/update in question.

Gert-Jan


Mike W wrote: