Prev: selecting and inserting existing and non existing rows into tableparameters and variables
Next: Default values not being applied on insert in SQL 2005 virtual server
From: Cal Who on 29 Mar 2010 13:53
I know I need a course in SQL but I need to fix this before that happens.
Below is a procedure that use to work before I changed ASC to DESC in four
places.
I probably needed to do more to make it descend instead of ascend.

Now I always get the same data returned.
I believe it is the data at the end of the stored data (i.e, the last page)
even when the desired page is 1
If you see what is wrong I'd sure like to know.

Maybe you can help me understand the procedure a little.
Does the IF get run and then the
SELECT @rowCount=COUNT(*) FROM announcements

and then the

SET ROWCOUNT @pageSize

and then the

SELECT id, itemdate, title, description FROM Announcements

Is that the way it happens?

Is the

SELECT @rows = (@pageNum-1) * @pageSize

simply storing a value in @rows

Seems like it should be a SET



Thanks in advance for any help




CREATE PROCEDURE PagedAnnouncementList

(

@pageNum INT = 1,

@pageSize INT = 10

)


AS

DECLARE @rows INT

DECLARE @keydate DATETIME

DECLARE @keyid INT

DECLARE @rowCount FLOAT /* yes we need a float for the math */


IF @pageNum = 1

BEGIN

SET @keydate= 0

SET @keyid=0

END

ELSE

BEGIN

/* get the values for the date and row */

SELECT @rows = (@pageNum-1) * @pageSize

SET ROWCOUNT @rows

SELECT @keydate=itemdate, @keyid=id FROM announcements ORDER BY itemdate
DESC, id DESC

END

SELECT @rowCount=COUNT(*) FROM announcements

SET ROWCOUNT @pageSize

SELECT id, itemdate, title, description FROM Announcements

WHERE (itemdate > @keydate OR

(itemdate = @keydate) AND (id > @keyid))

ORDER BY itemdate DESC, id DESC

RETURN CEILING(@rowCount/@pageSize)


From: Cal Who on 29 Mar 2010 15:04

" Cal Who" <CalWho(a)roadrunner.com> wrote in message
news:uW9J4i2zKHA.2552(a)TK2MSFTNGP04.phx.gbl...
>I know I need a course in SQL but I need to fix this before that happens.
> Below is a procedure that use to work before I changed ASC to DESC in four
> places.
> I probably needed to do more to make it descend instead of ascend.
>
Now I always get the similar data returned.
I believe the data is:
For the first page
the earliest 10 items
When the desired page is 2 (second and last)
same as above except it missing the last item (9 items)
> If you see what is wrong I'd sure like to know.
>
First priority is to fix the code, but
> Maybe you can help me understand the procedure a little.
> Does the IF get run and then the
> SELECT @rowCount=COUNT(*) FROM announcements
>
> and then the
>
> SET ROWCOUNT @pageSize
>
> and then the
>
> SELECT id, itemdate, title, description FROM Announcements
>
> Is that the way it happens?
>
> Is the
>
> SELECT @rows = (@pageNum-1) * @pageSize
>
> simply storing a value in @rows
>
> Seems like it should be a SET
>
>
>
> Thanks in advance for any help
>
>
>
>
> CREATE PROCEDURE PagedAnnouncementList
>
> (
>
> @pageNum INT = 1,
>
> @pageSize INT = 10
>
> )
>
>
> AS
>
> DECLARE @rows INT
>
> DECLARE @keydate DATETIME
>
> DECLARE @keyid INT
>
> DECLARE @rowCount FLOAT /* yes we need a float for the math */
>
>
> IF @pageNum = 1
>
> BEGIN
>
> SET @keydate= 0
>
> SET @keyid=0
>
> END
>
> ELSE
>
> BEGIN
>
> /* get the values for the date and row */
>
> SELECT @rows = (@pageNum-1) * @pageSize
>
> SET ROWCOUNT @rows
>
> SELECT @keydate=itemdate, @keyid=id FROM announcements ORDER BY itemdate
> DESC, id DESC
>
> END
>
> SELECT @rowCount=COUNT(*) FROM announcements
>
> SET ROWCOUNT @pageSize
>
> SELECT id, itemdate, title, description FROM Announcements
>
> WHERE (itemdate > @keydate OR
>
> (itemdate = @keydate) AND (id > @keyid))
>
> ORDER BY itemdate DESC, id DESC
>
> RETURN CEILING(@rowCount/@pageSize)
>
>


 | 
Pages: 1
Prev: selecting and inserting existing and non existing rows into tableparameters and variables
Next: Default values not being applied on insert in SQL 2005 virtual server