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

Question

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

elegant? No. But you can say `select @NumRows = iif(@NumRows = 0, 2147483648, @NumRows)` before plugging it into your `top` clause. That said, that may very well give you bad query plans. Unless you have thousands of procedures you need to do find/replace in, you might want consider the extra effort of specifying the two different code paths. Your mileage may vary. — Xedni, Sep 15 '17 at 17:40
I would caution against using multiple execution paths. It can lead to very interesting performance problems. Gail Shaw has a great blog post on the topic here. http://www.sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/ — Sean Lange, Sep 15 '17 at 18:19
@SeanLange thanks for the link. I read through it and understood the possibility of different execution plans. So if I dont use different execution paths, that means I would always use `TOP(N)`. Then I need to check the N value to see if it is 0 or not. If it is 0, i could make `NumRows` = 2147483647 or `NumRows` = count(*) from table, what effect would this have on the query plan? — Dongminator, Sep 15 '17 at 20:05
`SET ROWCOUNT` is not deprecated for `SELECT` statements, only for INSERT, UPDATE, and DELETE — Martin Smith, Sep 15 '17 at 21:32

score 1 · Answer 1 · answered Sep 15 '17 at 17:45

1

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.

answered Sep 15 '17 at 17:45

Lukasz Szozda

162,964
23
234
275

Thanks for your answer! Could you elaborate on the possible performance implications? If you could share a few links, I can read those as well. – Dongminator Sep 15 '17 at 20:06

Sean Lange · Accepted Answer · 2017-09-15T21:00:42.950

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

Thanks for the suggestion of setting default to 2147483648. I also editted my original question to include how the stored procedure looks like. I think if current default is 0, we can change it to 2147483648. But if the default is currently not 0, then should we do, `if @NumRows = 0, set @NumRows = 2147483648`? Or simply use this IF statement in all default cases? — Dongminator, Sep 15 '17 at 20:39
See my edit for an easier way than using an IF statement here. — Sean Lange, Sep 15 '17 at 21:01

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

2 Answers2