sql - 从 SQL 查询返回两组结果

我的存储过程是这样的:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Product_Search]
(
    /* Optional Filters for Dynamic Search*/
   @ProductName NVARCHAR(200)=NULL,

   /*– Pagination Parameters */
   @PageNo INT = 1,
   @PageSize INT = 10,

   /*– Sorting Parameters */
   @SortColumn NVARCHAR(20) = 'ProductAddedDate',
   @SortOrder NVARCHAR(4)='ASC',
   @PostCodes NVARCHAR(400)=NULL
)
AS
BEGIN
    /*–Declaring Local Variables corresponding to parameters for modification */
    DECLARE
        @lPageNbr INT,
        @lPageSize INT,
        @lSortCol NVARCHAR(20),
        @lFirstRec INT,
        @lLastRec INT,
        @lTotalRows INT

    /*Setting Local Variables*/

    SET @lPageNbr = @PageNo
    SET @lPageSize = @PageSize
    SET @lSortCol = LTRIM(RTRIM(@SortColumn))

    SET @lFirstRec = ( @lPageNbr - 1 ) * @lPageSize
    SET @lLastRec = ( @lPageNbr * @lPageSize + 1 )
    SET @lTotalRows = @lFirstRec - @lLastRec + 1;

    WITH CTE_Results AS
    (
        SELECT ROW_NUMBER() OVER
            (
                ORDER BY
                    CASE WHEN @lSortCol = 'ProductAddedDate' AND @SortOrder='ASC'
                        THEN ProductName
                    END ASC,
                    CASE WHEN @lSortCol = 'ProductAddedDate' AND @SortOrder='DESC'
                         THEN ProductName
                    END DESC,
                     CASE WHEN (@lSortCol = 'Price' AND @SortOrder='ASC')
                              THEN Price
                    END ASC,
                    CASE WHEN @lSortCol = 'Price' AND @SortOrder='DESC'
                             THEN Price
                    END DESC
           ) AS ROWNUM,
            Count(*) over () AS TotalCount,
            Id as ProductId ,
            ProductName,
            ProductDescription,
            [Price] ,
            [FairName] ,
            [FairDescription] ,
            [StartDate],
            [EndDate]  ,
            [FairLogo] ,
            [ProductAddedDate],
            ProductCategory_Id,
            FairId,
            IsHeroItem,
            ProductSubCategoryId ,
            SubCategoryName,
            MainCategoryName
        FROM wf_view_Products
        WHERE
            (@ProductName is NULL OR ProductName like '%'+ @ProductName+ '%') AND
            (@PostCodes is NULL OR PostCode IN( SELECT CAST(Item AS INTEGER) FROM dbo.SplitString(@PostCodes, ',')))
    )
    SELECT ProductId,
        ROWNUM,
        ProductCategory_Id,
        IsHeroItem,
        ProductName,
        ProductDescription,
        [Price],
        [FairName],
        [FairDescription],
        [StartDate],
        [EndDate],
        [FairLogo],
        [ProductAddedDate],
        FairId
    FROM CTE_Results AS CPC
    WHERE ROWNUM > @lFirstRec
        AND ROWNUM < @lLastRec
    ORDER BY ROWNUM ASC

    SELECT ProductId
    FROM CTE_Results AS Categories
    WHERE 1 = 1
END

当我删除此行时，一切正常，否则会引发错误:



任何人都可以让我知道我该如何解决这个问题？

最佳答案

正如评论中所建议的，您可以使用临时表(如果您愿意，甚至可以使用表变量，但在这种情况下，您必须定义表的结构)。

这是您编辑为使用临时表的存储过程:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Product_Search]
(
    /* Optional Filters for Dynamic Search*/
   @ProductName NVARCHAR(200)=NULL,

   /*– Pagination Parameters */
   @PageNo INT = 1,
   @PageSize INT = 10,

   /*– Sorting Parameters */
   @SortColumn NVARCHAR(20) = 'ProductAddedDate',
   @SortOrder NVARCHAR(4)='ASC',
   @PostCodes NVARCHAR(400)=NULL
)
AS
BEGIN
    /*–Declaring Local Variables corresponding to parameters for modification */
    DECLARE
        @lPageNbr INT,
        @lPageSize INT,
        @lSortCol NVARCHAR(20),
        @lFirstRec INT,
        @lLastRec INT,
        @lTotalRows INT

    /*Setting Local Variables*/

    SET @lPageNbr = @PageNo
    SET @lPageSize = @PageSize
    SET @lSortCol = LTRIM(RTRIM(@SortColumn))

    SET @lFirstRec = ( @lPageNbr - 1 ) * @lPageSize
    SET @lLastRec = ( @lPageNbr * @lPageSize + 1 )
    SET @lTotalRows = @lFirstRec - @lLastRec + 1;

    SELECT ROW_NUMBER() OVER
        (
            ORDER BY
                CASE WHEN @lSortCol = 'ProductAddedDate' AND @SortOrder='ASC'
                    THEN ProductName
                END ASC,
                CASE WHEN @lSortCol = 'ProductAddedDate' AND @SortOrder='DESC'
                     THEN ProductName
                END DESC,
                 CASE WHEN (@lSortCol = 'Price' AND @SortOrder='ASC')
                          THEN Price
                END ASC,
                CASE WHEN @lSortCol = 'Price' AND @SortOrder='DESC'
                         THEN Price
                END DESC
       ) AS ROWNUM,
        Count(*) over () AS TotalCount,
        Id as ProductId ,
        ProductName,
        ProductDescription,
        [Price] ,
        [FairName] ,
        [FairDescription] ,
        [StartDate],
        [EndDate]  ,
        [FairLogo] ,
        [ProductAddedDate],
        ProductCategory_Id,
        FairId,
        IsHeroItem,
        ProductSubCategoryId ,
        SubCategoryName,
        MainCategoryName
    INTO #tmpTable --create a temp table on the fly
    FROM wf_view_Products
    WHERE
        (@ProductName is NULL OR ProductName like '%'+ @ProductName+ '%') AND
        (@PostCodes is NULL OR PostCode IN( SELECT CAST(Item AS INTEGER) FROM dbo.SplitString(@PostCodes, ',')))

    SELECT ProductId,
        ROWNUM,
        ProductCategory_Id,
        IsHeroItem,
        ProductName,
        ProductDescription,
        [Price],
        [FairName],
        [FairDescription],
        [StartDate],
        [EndDate],
        [FairLogo],
        [ProductAddedDate],
        FairId
    FROM #tmpTable AS CPC --select from temp table  instead of CTE
    WHERE ROWNUM > @lFirstRec
        AND ROWNUM < @lLastRec
    ORDER BY ROWNUM ASC

    SELECT ProductId
    FROM #tmpTable AS Categories --select from temp table  instead of CTE
    WHERE 1 = 1
END

关于sql - 从 SQL 查询返回两组结果，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/43094792/