Jeff's paging solution help - 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 2000 Forums

Transact-SQL (2000)

Jeff's paging solution help

Author

Topic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-17 : 19:58:29

I am trying to help rewrite some code for a legacy application. One of the stored procedures uses dynamic sql. It also uses a temp table for paging. I have already removed the dynamic sql, but I need help removing the temp table using Jeff's paging solution.

Here is what I have so far:



SET NOCOUNT ON

DECLARE @startingID int
DECLARE @a int
DECLARE @b int 

SET @a = 1 
SET @b = 400 

CREATE TABLE END_USER
(
[ID] INT IDENTITY(1, 1) NOT NULL,
UserID VARCHAR(10) NOT NULL,
TypeCD CHAR(2) NULL
)

INSERT INTO END_USER(UserID, TypeCD)
SELECT 'robvolk', 'AD'
UNION ALL
SELECT 'nr', 'CL'
UNION ALL
SELECT 'tduggan', 'CL'
UNION ALL
SELECT 'X002548', 'CL'
UNION ALL
SELECT 'Merkin', 'AD'
UNION ALL
SELECT 'graz', 'AD'

SET rowcount @a 
SELECT @startingID = ID 
FROM END_USER ORDER BY ID ASC 

SET @b = @b - @a 
SET rowcount @b 

SELECT 	[ID], 
	UserID, 
	TypeCD
FROM END_USER 
WHERE [ID] >= @startingID 
ORDER BY UserID ASC

DROP TABLE END_USER

SET ROWCOUNT 0

The above produces this:



ID          UserID     TypeCD 
----------- ---------- ------ 
6           graz       AD
5           Merkin     AD
2           nr         CL
1           robvolk    AD
3           tduggan    CL
4           X002548    CL

I need to add a column called Row. The data in the column should be 1 - NumOfRecords. I could easily do this with a temp table and an IDENITY column, but that obviously defeats the purpose here. I think I can use a Tally table, but I can't figure it out. Here is what the result set should look like:



Row         ID          UserID     TypeCD 
----------- ----------- ---------- ------ 
1           6           graz       AD
2           5           Merkin     AD
3           2           nr         CL
4           1           robvolk    AD
5           3           tduggan    CL
6           4           X002548    CL

Tara

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-03-17 : 20:07:50

Tara,

Does this cut it....


SELECT [ID], UserID, TypeCD,  (SELECT COUNT(*) FROM END_USER A WHERE A.[ID] >= @startingID AND A.UserID <= X.UserID)
FROM END_USER X
WHERE [ID] >= @startingID 
ORDER BY UserID ASC

DavidM

"An Ugg Boot is a generic Australian term that has been in use for nearly 100 hundred years. Now some coporate wanker has trademarked it.. "

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-17 : 20:13:57

Yes it does. Thanks for the help David!

I don't think that the application really needs the column, but since it is legacy code we can't go back and change it. So if I change the stored procedure, it needs to produce the same result set as the previous version.

I am hoping that I can utilize Jeff's paging solution for the other stored procedures as well. This one was the most simple so I started there.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-18 : 14:14:35

If I change @b to 6, I only get back 5 rows. Isn't it supposed to return 6 rows and not 5? Does Jeff's solution not include the last row? What am I missing here?

Here's the updated code:



SET NOCOUNT ON

DECLARE @startingID int
DECLARE @a int
DECLARE @b int 

SET @a = 1 
SET @b = 6 

CREATE TABLE END_USER
(
[ID] INT IDENTITY(1, 1) NOT NULL,
UserID VARCHAR(10) NOT NULL,
TypeCD CHAR(2) NULL
)

INSERT INTO END_USER(UserID, TypeCD)
SELECT 'robvolk', 'AD'
UNION ALL
SELECT 'nr', 'CL'
UNION ALL
SELECT 'tduggan', 'CL'
UNION ALL
SELECT 'X002548', 'CL'
UNION ALL
SELECT 'Merkin', 'AD'
UNION ALL
SELECT 'graz', 'AD'

SET rowcount @a 
SELECT @startingID = ID 
FROM END_USER ORDER BY ID ASC 

SET @b = @b - @a 
SET rowcount @b 

SELECT 	(SELECT COUNT(*) FROM END_USER A WHERE A.[ID] >= @startingID AND A.UserID <= X.UserID) AS Row,
	[ID], 
	UserID, 
	TypeCD
FROM END_USER X
WHERE [ID] >= @startingID 
ORDER BY UserID ASC

DROP TABLE END_USER

SET ROWCOUNT 0

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-18 : 14:17:17

Isnt' the following setting the rowcount to 5?

SET @b = @b - @a

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-18 : 14:21:20

Yes, but I'm wondering why that part is done. In one of his posts, he has @a = 200 and @b = 250. So that would mean @b gets set to 50. But if you want to display rows 200-250, that's 251 rows. So I guess I'll just increment by 1.

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-18 : 14:28:44

I see what you mean.

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-18 : 14:51:04

Hey...why do I always get left out?

@b = 7

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-18 : 15:14:27

It turns out I need Row column to be like this:



Row         ID          UserID     TypeCD 
----------- ----------- ---------- ------ 
2           6           graz       AD
3           5           Merkin     AD
4           2           nr         CL
5           3           tduggan    CL
6           4           X002548    CL

So the first row would be @a and the next row would be @a + 1, ..., the last row would be @b. How would I do that? I thought I needed 1-NumOfRows. But I was wrong. It's @a-@b.

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-18 : 19:51:05

Tara, I was just re-reading Jeff's blog about this paging technique and the behavior you mentioned was discussed in the feedback section.

http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-18 : 19:53:13

Thanks Jay. I'll have a look at it.

Got a solution for my last problem though? Not related to Jeff's solution, but rather need to add a column to the result set.

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-18 : 19:56:55

How about:


SELECT 	(SELECT COUNT(*) + 1 FROM END_USER A WHERE A.[ID] >= @startingID AND A.UserID <= X.UserID) AS Row,
	[ID], 
	UserID, 
	TypeCD
FROM END_USER X
WHERE [ID] >= @startingID 
ORDER BY UserID ASC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-18 : 20:01:44

~~It works.~~ Thanks Jay.

I'm just not very good at coming up with those kinds of queries. I can't even picture it, let alone write it. But maybe after seeing yours and David's solutions, it'll help improve my knowledge.

Valter mentions in Jeff's feedback about how to get DESC to work. My overall query needs to use a dynamic ORDER BY. I was having problems with it earlier, but I'll take another crack at it tomorrow.

EDIT:

Actually it doesn't work. I need the Row column to be @a through @b. So if @a = 100 and @b = 120, then Row should be 100-120. If @a = 5 and @b = 200, then Row should be 5 - 200.

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-18 : 20:04:57

No worries Tara. I have learned so much from your posts its nice to be able to return the favor.

EDIT: Or at least try..

In Jeffs feedback it was the user "TheKode" which makes a suggestion to resolve the offset of rowcount.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-18 : 20:15:11

This is what I came up with:


SELECT 	(SELECT COUNT(*) + @a - 1 FROM END_USER A WHERE A.[ID] >= @startingID AND A.UserID <= X.UserID) AS Row,
	[ID], 
	UserID, 
	TypeCD
FROM END_USER X
WHERE [ID] >= @startingID 
ORDER BY UserID ASC

It seems to work at least for the sample data. I can't quite yet run it against our dev environment as there are a few things wrong with my query still that aren't shown here.

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-18 : 21:34:28

I noticed you had said you need to provide Dynamic order by capability. But I think you will have trouble implementing Jeffs solution in this case. Let me explain why:

I was playing around with this query a little and noticed something kinda wierd...
This is producing some funky results in the sample.

For example if I want to see records 3-6, based on the order by [USERID], I would expect: the following resultset:

2	nr	CL
1	robvolk	AD
5   	tduggan	CL
4	X002548	CL

But what I get is:

6	graz	AD
5	Merkin	AD
3	tduggan	CL
4	X002548	CL

I believe Jeff mentions that this technique will cause some wacky results and I think this is why. I am thinking the temp table technique might be less hassles here. IMHO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-18 : 22:51:00

Hmmm, I'll have to take a look at that tomorrow morning. Maybe Jeff has some insight as to what is occurring.

Tara

xpandre
Posting Yak Master

212 Posts

Posted - 2004-03-19 : 03:17:46

wow..this seems kewl for paging....paging in just 1 sql!!




select * from END_USER

ID          UserID     TypeCD 
----------- ---------- ------ 
1           robvolk    AD
2           nr         CL
3           tduggan    CL
4           X002548    CL
7           robvolk    AD
6           graz       AD
8           nr         CL
9           tduggan    CL
10          X002548    CL
11          graz       AD
12          robvolk    AD
13          nr         CL
14          tduggan    CL
15          X002548    CL
16          graz       AD
17          robvolk    AD
18          nr         CL
19          tduggan    CL
20          X002548    CL

select id,userid,typecd,c.a from 
(select *,(select count(1)+1 from  END_USER a where a.id<b.id) a
from END_USER b  ) c
where c.a>10 and c.a<=20
order by c.a desc

id          userid     typecd a           
----------- ---------- ------ ----------- 
21          graz       AD     20
20          X002548    CL     19
19          tduggan    CL     18
18          nr         CL     17
17          robvolk    AD     16
16          graz       AD     15
15          X002548    CL     14
14          tduggan    CL     13
13          nr         CL     12
12          robvolk    AD     11

(10 row(s) affected)

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-03-19 : 04:40:35

xpandre,

The performance of a method like that is terrible when the data set is large..
Personally I have found either using dynamic sql via the nested TOP method or the SET ROWCOUNT method are far more server firendly...

DavidM

"An Ugg Boot is a generic Australian term that has been in use for nearly 100 hundred years. Now some coporate wanker has trademarked it.. "

xpandre
Posting Yak Master

212 Posts

Posted - 2004-03-19 : 05:41:54

yess...i just tested with 1/2 million records...n booom:-(

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-19 : 10:44:20

Tara, what is occuring here is the following:

SET rowcount @a 
SELECT @startingID = ID 
FROM END_USER ORDER BY ID ASC

is selecting the starting row to be the first row based on @a. (ie in the 3-6 example; @a=3) ordered by ID ASC. Now the code selects the 4 records where [ID] >= 3. But because recordset is order by USERID it looks like the following:

6	graz	AD
5	Merkin	AD
2	nr	CL
1	robvolk	AD
3	tduggan	CL
4	X002548	CL

So the conditions are being met in returning the resultset where ID>=3 ordered by USERID ASC:

6	graz	AD
5	Merkin	AD
3	tduggan	CL
4	X002548	CL

Just not sure if that is the expected result??

For this reason I believe IDENTITY, or some other row number, must be assigned to the ORDERED BY resultset and then the paged results can be selected.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources