当n可能为0时，将SET ROWCOUNT n转换为TOP（n）

Question

我们当前正在使用SQL server 2016.基于微软页面Deprecated Database Engine Features in SQL Server 2016，SET ROWCOUNT已被弃用。所以我们试图将所有SET ROWCOUNT N报表转换为TOP(N)报表。看起来很容易，但我们遇到了一个情况，其中N是存储过程中的一个参数，默认为0.

因此在旧代码中，它类似于SET ROWCOUNT @NumRows。如果@NumRows为0，则表示关闭SET ROWCOUNT选项，因此以下查询将返回所有行。但是如果我们将其转换为TOP(@NumRows)，那意味着它将返回0行。为了避免这个问题，我们可以添加额外的IF条件，如果@NumRows为0，则将@NumRows设置为一个很大的数字。或者我们可以添加额外的IF条件，如果@NumRows为0，那么我们使用SELECT没有TOP，否则我们像往常一样做SELECT TOP(N)。

但是这些解决方案中的任何一个都会在存储过程中添加额外的代码，所以我的问题是：考虑到N可能为0，是否有一种将SET ROWCOUNT N转换为TOP (N)的优雅方法？

更新：增加了存储过程模板

-- 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 

Answer 1

这听起来像你的程序定义是这样的。如果您发布了定义，那么在这里可以更容易地获得帮助。

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

    select Columns 
    from Table 

假设定义如上所述，您可以将其更改为如下所示。

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 

请注意，使用TOP时，您需要指定一个顺序或您无法知道将返回哪些行。

- 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; 

Rextester Demo

注意的可能性能影响。另外TOP没有明确的ORDER BY可能会返回不同的结果集。