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

Question

We are currently using SQL Server 2016. Based on the page

Answer 1

It seems that defining your procedure is this. It would be 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 similar to above, you can 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 indicate the order or you do not know which rows will be returned.

- EDIT -

. , . IF , . , lad2025.

select top(isnull(nullif(@NumRows, 0), 2147483647)) Columns
from Table

Answer 2

/ TOP :

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;

-

. TOP ORDER BY .