SQL stored procedure SET output param using COUNT(*) ON a CTE

Question

I'm using a stored procedure with a CTE and doing some paging. I also want to return an output parameter with the total count of the returned query before my paging. My problem is that I get an error that "OrderedSet" is not a valid object name.

    @ft INT,
    @page INT,
    @pagesize INT,
    @count INT OUTPUT
AS
BEGIN

DECLARE @offset INT
SET @offset = @page * @pagesize
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    WITH OrderedSet AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Id DESC) AS 'Index'
    FROM tbl_BulkUploadFiles buf
    WHERE
    buf.FileType = @ft )
    SELECT * FROM OrderedSet WHERE [Index] BETWEEN @offset AND (@offset + @pagesize)
    SET @count = (SELECT COUNT(*) FROM OrderedSet)
END

So my issue is on the last line, error is that last OrderedSet is not a valid object name.

Thanks in advance for any help!

Answer 1

Here are 2 approaches that avoid copying and pasting all the CTEs multiple times.

Return total rows as column of result set

Benefit here is that you can calculate total rows without multiple queries and temp tables, but you have to add logic to your front end to get the total row count from the first row of the result set before iterating over it to display the paged set. Another consideration is that you must account for no rows being returned, so set your total row count to 0 if no rows returned.

;WITH OrderedSet AS (
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY Id DESC) AS Seq,
        ROW_NUMBER() OVER (ORDER BY Id) AS SeqRev
    FROM tbl_BulkUploadFiles buf
    WHERE buf.FileType = @ft
)
    SELECT *, Seq + SeqRev - 1 AS [TotalCount]
    FROM OrderedSet
    WHERE Seq BETWEEN @offset AND (@offset + @pagesize)

Utilize a temp table

While there is a cost of a temp table, if your database instance follows best practices for tempdb (multiple files for multi-cores, reasonable initial size, etc), 200k rows may not be a big deal since the context is lost after the stored proc completes, so the 200k rows don't exist for too long. However, it does present challenges if these stored procs are called quite often concurrently - doesn't scale too well. However, you are not keeping the entire table - just the paged rows, so hopefully your page sizes are much smaller than 200k rows.

The approach below tries to minimize the tempdb cost being able to calculate the row count by getting only the first row due to the method of ASC and DESC ROW_NUMBERs.

;WITH OrderedSet AS (
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY Id DESC) AS Seq,
        ROW_NUMBER() OVER (ORDER BY Id) AS SeqRev
    FROM @buf buf --tbl_BulkUploadFiles buf
    WHERE buf.FileType = @ft
)
    SELECT * INTO #T
    FROM OrderedSet
    WHERE Seq BETWEEN @offset AND (@offset + @pagesize)
SET @count = COALESCE((SELECT TOP 1 SeqRev + Seq - 1 FROM #T), 0)
SELECT * FROM #T

Note: The method used above for calculating row counts was adapted from How to reference one CTE twice? and http://www.sqlservercentral.com/articles/T-SQL/66030/.

Answer 2

Only alternative I see is repeating the query as inline view

  select @count = numrows FROM 
  (
    SELECT count(*) as numrows, 
    ROW_NUMBER() OVER (ORDER BY Id DESC) AS 'Index'
    FROM tbl_BulkUploadFiles buf
    WHERE
    buf.FileType = @ft 
    ) XXX WHERE [Index] BETWEEN @offset AND (@offset + @pagesize)

Answer 3

You can't use the CTE in more than one select statement. From the MSDN docs (talking about the CTE itself).

This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.

You either need to run the CTE twice (probably a bad idea) or select the results of the CTE into a temp table and then select the paged data from that along with the total count.