Getting rowcount of a common table expression CTE into a parameter for paging
My friend here is coding a web page with a datagrid that has paging. We were able to get the total pagecount into a column as a window function but we can't figure out how to get it into a parameter. Seeing the code will make more sense:
DECLARE @StartRow INT
DECLARE @EndRow INT
DECLARE @PerPage INT
DECLARE @PageNumber int
SET @PerPage = 30
SET @PageNumber = 1
SET @StartRow = ( ( @PageNumber - 1 ) * @PerPage ) + 1
SET @EndRow = ( ( @PageNumber ) * @PerPage ) ;
WITH cte
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY Name ) [row]
, Name
, COUNT(*) OVER ( ) AS [rowcount]
FROM table )
SELECT row, Name, ( [rowcount] / @PerPage ) + 1 AS [pages]
FROM cte
WHERE row BETWEEN @StartRow AND @EndRow
OR ( @PageNumber = -1 )
I can't get the parameter from the last select because you can't set parameters when you're also returning values. I was hoping there's be some way to do t开发者_高级运维his but in the meantime (which may be an equally good solution) we are just returning this in the dataset and pulling the pages count in code from the dataset instead of through an output parameter. Make sense? Let me know if you know of a way to get this into a parameter! Thanks!
Unfortunately, all avenues reduce to the same thing: trying to set a value in a select while also outputting data, a verboten combination. My eventual solution to using an output parameter for the total count was to insert the paged data into a table variable, which made it possible to both select the data and set the output parameter. (A temporary table would also have sufficed.)
My answer was to add another cte, as I had a reqirement to put total in last row:
Declare @Page INT
SET @Page = 5
;with MainData
AS
(
select
name
from
sys.tables
)
,MainDataWithCount
AS
(
select
name
,row_number() over (ORDER BY name) AS Row
from
Maindata
)
select
MainDataWithCount.name
,MainDataWithCount.Row
,MainDataWithCount.row / @Page
from
MainDataWithCount
This small change should do it.
, COUNT(*) OVER (PARTITION BY NULL) AS [rowcount]
精彩评论