Pagination and Row count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pagination and Row count

Author

Topic

vandalo
Starting Member

4 Posts

Posted - 2008-10-01 : 12:32:35

Hello, I am using this T-SQL (inside a stored procedure) for pagination:

;WITH TestTable(RowNumber, Col01, Col02, Col03) AS (
SELECT
ROW_NUMBER() OVER (ORDER BY Col02),
Table001.Col01,
Table001.Col02,
Table001.Col03
COUNT(Table001.Col01) OVER () AS [total_rows]
FROM
Table001
WHERE
Table001.Col02 = '01' AND Table001.Col03 = 1
)

SELECT * FROM TestTable WHERE RowNumber BETWEEN @FirstRow AND @LastRow

I would like my stored procedure to return a list of paginated records and the total number of rows in the query (main one) as a return parameter.

The only solution I have found is to execute another COUNT query which sets a variable.
I would like (if possible) to use the same query defined in the CTE expression so that I don't have to duplicate my code.

Is there any solution for that :-s

thanks in advance.

Alberto

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 12:40:30

[code];WITH TestTable(RowNumber, Col01, Col02, Col03) AS (
SELECT
ROW_NUMBER() OVER (ORDER BY Col02),
Table001.Col01,
Table001.Col02,
Table001.Col03
COUNT(Table001.Col01) OVER () AS [total_rows]
FROM
Table001
WHERE
Table001.Col02 = '01' AND Table001.Col03 = 1
)

SELECT *,
(SELECT MAX(RowNumber) FROM TestTable) AS TotalRows
FROM TestTable WHERE RowNumber BETWEEN @FirstRow AND @LastRow[/code]

vandalo
Starting Member

4 Posts

Posted - 2008-10-01 : 17:44:44

Thanks for your reply visakh16.
I would like to return the TotalRows as return (or output) parameter cause my application need to know in advance how many records are stored in the query.
In your example it seems that the new column TotalRows is inside the recordset.

Alberto

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 03:42:02

[code]CREATE PROC PaginationProc
@FirstRow int,
@LastRow int,
@TotalRows int OUTPUT
AS
;WITH TestTable(RowNumber, Col01, Col02, Col03) AS (
SELECT
ROW_NUMBER() OVER (ORDER BY Col02),
Table001.Col01,
Table001.Col02,
Table001.Col03
COUNT(Table001.Col01) OVER () AS [total_rows]
FROM
Table001
WHERE
Table001.Col02 = '01' AND Table001.Col03 = 1
)

SELECT *
FROM TestTable WHERE RowNumber BETWEEN @FirstRow AND @LastRow

SELECT @TotalRows=MAX(RowNumber) FROM TestTable
GO[/code]

and call it like this

[code]DECLARE @TotalRowsRet int
EXEC PaginationProc 0,100,@TotalRowsRet OUTPUT--this will give you the paged results (rows between 1 and 100)

SELECT @TotalRowsRet--this will give you total rows value
[/code]

vandalo
Starting Member

4 Posts

Posted - 2008-10-02 : 04:06:57

Hello visakh16,

I am sorry to disappoint you but you can't call the CTE "TestTable" twice. After the first execution it expires and you get: "Invalid object name 'TestTable'"

thanks for your help, anyway

Alberto

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 04:38:19

quote:
Originally posted by vandalo

Hello visakh16,

I am sorry to disappoint you but you can't call the CTE "TestTable" twice. After the first execution it expires and you get: "Invalid object name 'TestTable'"

thanks for your help, anyway

Alberto

yup thats true. sorry i overlook it (in fact i thought i didnt realise its a CTE as name was test table ). why use CTE. you could use a temporary table.

CREATE PROC PaginationProc
@FirstRow int,
@LastRow int,
@TotalRows int OUTPUT
AS
SELECT 
ROW_NUMBER() OVER (ORDER BY Col02) AS RowNumber, 
Table001.Col01,
Table001.Col02,
Table001.Col03
COUNT(Table001.Col01) OVER () AS [total_rows]
INTO #TestTable
FROM 
Table001
WHERE
Table001.Col02 = '01' AND Table001.Col03 = 1


SELECT *
 FROM #TestTable WHERE RowNumber BETWEEN @FirstRow AND @LastRow

SELECT @TotalRows=MAX(RowNumber) FROM #TestTable
GO

and call it like this

DECLARE @TotalRowsRet int
EXEC PaginationProc 0,100,@TotalRowsRet OUTPUT--this will give you the paged results (rows between 1 and 100)

SELECT @TotalRowsRet--this will give you total rows value

vandalo
Starting Member

4 Posts

Posted - 2008-10-02 : 05:34:16

I don't love temporary tables at all. I read they have loads of problems.
I like the CTE cause I can nest them etc etc.

Thanks

Alberto

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 05:37:34

quote:
Originally posted by vandalo

I don't love temporary tables at all. I read they have loads of problems.
I like the CTE cause I can nest them etc etc.

Thanks

Alberto

but if you're using ctes you can just use them only once as told before.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-26 : 19:25:42

quote:
Originally posted by vandalo

I don't love temporary tables at all. I read they have loads of problems.
I like the CTE cause I can nest them etc etc.

Thanks

Alberto

We use temporary table in most of our mission critical applications that have high performance requirements and high SLA requirements. I'm not sure what you've read, but you need to rethink it. There are times to use temporary tables and there are times to avoid them. It just depends on the situation.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources