Return two sets of results from SQL query

Question

My stored procedure is 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 remove this line everything works, else it throws an error:

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

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

Because a CTE can only be accessed by the next query. At the point this query runs the CTE is out of scope and doesn't exist. And don't be scared to use some formatting for your queries so you can have some visual queues about what it is doing. — Sean Lange, Mar 29 '17 at 13:24
well, yes, as a CTE is only valid for a single SELECT, INSERT, UPDATE, or DELETE statement — Lamak, Mar 29 '17 at 13:25
So you'll have to copy your CTE and stick it above that last SQL statement as well. Or convert the CTE to a temp table so you can reference it more than once in your proc. Don't forget to add a semicolon after the first query since that's a complete statement. — JNevill, Mar 29 '17 at 13:31
@SeanLange is there any way that I can make some edit and make this return my second query results too — None, Mar 29 '17 at 13:33
@JNevill temp table idea looks good to me, Can you please paste it as an answer with necessary code? — None, Mar 29 '17 at 13:33
See the comment from @JNevill, that comment explains the two options for doing this. — Sean Lange, Mar 29 '17 at 13:34
Just get rid of the `WITH CTE_Results` and parantheses and use the `SELECT INTO #TempTableNAme FROM ` Then reference table `#TempTableName` instead of the CTE. See [here](http://stackoverflow.com/questions/16683758/how-to-create-a-table-from-select-query-result-in-sql-server-2008) for the syntax. — JNevill, Mar 29 '17 at 13:36

Andrea · Accepted Answer · 2017-03-29T14:23:51.983

As suggested in the comments you can use a temp table (or even a table variable if you prefer, but in this case you have to define the table's structure).

Here is your stored procedure edited to use a temp 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

Return two sets of results from SQL query

1 Answers1