开发者

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]

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜