Return two result sets from SQL query

Question

My stored procedure looks like this:

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

When I delete this line, everything works, otherwise it throws an error:

Msg 208, Level 16, State 1, Product_Search Procedure, Line 65
[Batch Start Line 2]
Invalid object name 'CTE_Results'

Can someone please let me know how I can solve this?

Answer 1

As indicated in the comments, you can use a temporary table (or even a table variable if you want, but in this case you must determine the structure of the table).

Here is your stored procedure edited to use a temporary table:

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