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
It sounds like your procedure definition is something like this. It would be a LOT easier to help here if you posted the definition.
Assuming the definition is like above you could change it to something like this.
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.