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

i'm using stored procedure cte , doing paging. want return output parameter total count of returned query before paging. problem error "orderedset" not valid object name.

    @ft int,     @page int,     @pagesize int,     @count int output begin  declare @offset int set @offset = @page * @pagesize     -- set nocount on added prevent result sets     -- interfering select statements.     set nocount on;      -- insert statements procedure here     orderedset (     select *, row_number() on (order id desc) 'index'     tbl_bulkuploadfiles buf         buf.filetype = @ft )     select * orderedset [index] between @offset , (@offset + @pagesize)     set @count = (select count(*) orderedset) end 

so issue on last line, error last orderedset not valid object name.

thanks in advance help!

here 2 approaches avoid copying , pasting ctes multiple times.

return total rows column of result set

benefit here can calculate total rows without multiple queries , temp tables, have add logic front end total row count first row of result set before iterating on display paged set. consideration must account no rows being returned, set total row count 0 if no rows returned.

;with orderedset (     select         *,         row_number() on (order id desc) seq,         row_number() on (order id) seqrev     tbl_bulkuploadfiles buf     buf.filetype = @ft )     select *, seq + seqrev - 1 [totalcount]     orderedset     seq between @offset , (@offset + @pagesize) 

utilize temp table

while there cost of temp table, if database instance follows best practices tempdb (multiple files multi-cores, reasonable initial size, etc), 200k rows may not big deal since context lost after stored proc completes, 200k rows don't exist long. however, present challenges if these stored procs called quite concurrently - doesn't scale well. however, not keeping entire table - paged rows, page sizes smaller 200k rows.

the approach below tries minimize tempdb cost being able calculate row count getting first row due method of asc , desc row_numbers.

;with orderedset (     select         *,         row_number() on (order id desc) seq,         row_number() on (order id) seqrev     @buf buf --tbl_bulkuploadfiles buf     buf.filetype = @ft )     select * #t     orderedset     seq between @offset , (@offset + @pagesize) set @count = coalesce((select top 1 seqrev + seq - 1 #t), 0) select * #t 

note: method used above calculating row counts adapted how reference 1 cte twice? , http://www.sqlservercentral.com/articles/t-sql/66030/.