忽略存储过程中的跳过/接收 [英] Ignore skip/take in stored procedure

问题描述

数据库中很少有大型存储过程.有@skip 和@take 参数.我可以使用这个存储过程并禁用@take 吗?我有时想选择所有行，但有时要选择@take 行.我可以使用诸如if else"之类的东西来禁用 @take(如果 @take 为 null - 例如)并且不复制主选择代码或不复制此存储过程而没有 @skip,@take 吗?

There are few big stored procedures in database. There are @skip and @take parameters. Can I use this stored procedure and disable @take? I want select all rows sometimes but sometimes @take rows. Can I use something like 'if else' for disabling @take, (if @take is null - for example) and not duplicate main select code or not duplicate this stored procedures without @skip,@take?

ALTER PROCEDURE ...
@Skip INT,
@Take INT
AS
BEGIN
SELECT col1, col2, ...
...
...
...
 FROM ...
 WHERE ... 
 ORDER BY ....

OFFSET @Skip ROWS 
FETCH NEXT @Take ROWS ONLY 

我想实现这样的东西(@skip，@take 只有当它通过时):

I want to achieve something like this (@skip, @take only when it is passed):

ALTER PROCEDURE ...
@Skip INT = null,
@Take INT = null
AS
BEGIN
SELECT col1, col2, ...
...
...
...
 FROM ...
 WHERE ... 
 ORDER BY ....
if (@skip IS NOT NULL AND @take IS NOT NULL)
    OFFSET @Skip ROWS 
    FETCH NEXT @Take ROWS ONLY 

推荐答案

接受默认的 NULL 值并将 @Skip 和 @Take 设置为0和省略参数时非常大的值.

Accept default NULL values and set @Skip and @Take to 0 and very big value when parameters are omitted.

ALTER PROCEDURE YourProcedure
    @Skip INT = NULL,
    @Take INT = NULL
AS
BEGIN

    IF @Skip IS NULL
        SET @Skip = 0

    IF @Take IS NULL
        SET @Take = 999999999

    SELECT
        YourColumn
    FROM
        YourTable
    ORDER BY
        SomeColumn
    OFFSET 
        @Skip ROWS 
    FETCH NEXT 
        @Take ROWS ONLY 

END

确保您的最大 @Take 值始终大于查询结果行.

Make sure that your max @Take value is always greater than your query result rows.

这篇关于忽略存储过程中的跳过/接收的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！