Convert SET ROWCOUNT n to TOP (n) when n could be 0

670 views Asked by At

We are currently using SQL server 2016. Based on the Microsoft page Deprecated Database Engine Features in SQL Server 2016, the SET ROWCOUNT is being deprecated. So we are trying to convert all SET ROWCOUNT N statements to TOP(N) statements. It seems very easy but we encountered a case where N is a parameter in a stored procedure, default to 0.

So in the old code, it is something like SET ROWCOUNT @NumRows. If @NumRows is 0, that means to turn off the SET ROWCOUNT option, so the following query will return all rows. But if we convert this to TOP(@NumRows), that means it will return 0 row. To avoid this problem, we could add extra IF condition, if @NumRows is 0, set @NumRows to a huge number. Or we could add extra IF condition, if @NumRows is 0, then we use SELECT without TOP, else we do SELECT TOP(N) as usual.

But either of these solution will add extra code in the stored procedures, so my question is: is there an elegant way to convert SET ROWCOUNT N to TOP (N) considering that N could be 0?

Update: added stored procedure template

-- Default to 0, in this case, SET ROWCOUNT 0 will return all result
-- But if we change it to TOP(@rows), it returns no result.
CREATE PROCEDURE Proc1  
  @rows int = 0  
AS  
SET ROWCOUNT @rows
SELECT Col1 FROM table1
ORDER BY Col2
SET ROWCOUNT 0

-- In this case, the default is not 0
-- But the program that calls this stored procedure could pass in value 0. 
-- We also don't want to change default value for this stored procedure. 
-- So I think in this case, we may have to add IF @rows = 0, SET @rows = huge_number
CREATE PROCEDURE Proc2
  @rows int = 10
AS
SET ROWCOUNT @rows
SELECT Col3 FROM table2
ORDER BY Col4
SET ROWCOUNT 0
2

There are 2 answers

2
Sean Lange On BEST ANSWER

It sounds like your procedure definition is something like this. It would be a LOT easier to help here if you posted the definition.

create procedure MyProc
(
    @NumRows int = 0
)
as 
    if @NumRows > 0
        set rowcount @NumRows
    else
        set rowcount 0

    select Columns
    from Table

Assuming the definition is like above you could change it to something like this.

create procedure MyProc
(
    @NumRows int = 2147483648 --max value for an int. This could be a bigint also if you have LOTS of data.
)
as 
    select top(@NumRows) Columns
    from Table

Please note that when using TOP you need to specify an order by or you have no way of knowing what rows will be returned.

--EDIT--

Thank you for the code example. It seems I was pretty close in my guess here. You don't have to use an IF statement here, you can accomplish this a little easier directly in your query. This is similar to the answer posted by lad2025.

select top(isnull(nullif(@NumRows, 0), 2147483647)) Columns
from Table
1
Lukasz Szozda On

You could use subquery/expression in TOP clause instead of specific value:

DECLARE @param INT = 0;

SELECT TOP (SELECT IIF(@param=0,2000000, @param)) * FROM sys.objects;
SELECT TOP (IIF(@param=0,2000000, @param)) * FROM sys.objects;

Rextester Demo

Be aware of possible performance implications. Also TOP without explicit ORDER BY may return different resultsets.