Start Free Trial

asked on

stored procedure help to return paginated results

hi there,
im trying to use a stored procedure thats a simple select statement that needs to paginate returned data...
im used to LIMIT 0,1 in MySQL although im using MS SQL in this case.

the table id is incremented automatically...

my question is: I need an example of a stored procedure that receives a parameter that indicates the page number together with a second parameter that indicates how many records to return. Then within this example I need the necessary calculations to return the appropriate range of data.

ie.
table schema:

id name
1 aaaaaaaa
2 bbbbbbbbbbbbb
3 ccccccccccc
4 dddddddddd
5 eeeeeee
6 fffffffff
7 gggggggggg
8 hhhhhhhhhh
9 kkkkkkkkkkkk
10 ooooooooooooo

exec usp_GetResults @pageno = 1, @qty = 3

results:
id name
1 aaaaaaaa
2 bbbbbbbbbbbbb
3 ccccccccccc

exec usp_GetResults @pageno = 2, @qty = 3

results:
id name
4 dddddddddd
5 eeeeeee
6 fffffffff

exec usp_GetResults @pageno = 3, @qty = 2

results:
5 eeeeeee
6 fffffffff

hope this is clear, let me know if not,
thanks

Open in new window

If the ID identification can be used for the numbering ... this should do the trick:

Hope this helps ...

create stored procedure usp_GetResults
    @pageno int
,   @qty int
as
begin
    select  *
    from    yourtable
    where ID between (((@pageno-1)*@qty)+1) and (@pageno*qty)
end
go

Open in new window

ASKER

this is along the lines I was trying although the id's may have missing numbers, the data might have id values like so,

1,
2,
3,
4,
5,
10,
348,
490,
983
...

wouldnt the approach you highlighted only work if the id's where like so,
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11
...

??

this is probably what you are after

create proc usp_GetResults (@pageno int , @qty int)
as
set nocount on
declare @start int
if @pageno > 1
begin
  declare @skip int set @skip = @pageno * @qty
  set rowcount @skip
  select @start = ID from mytable order by ID
  set rowcount 0
end
 
set nocount on
set rowcount @qty
select f1, f2, f3, f4
from mytable
where id > isnull(@start, 0)
order by ID
set rowcount 0

Open in new window

oops... please replace line 7:

declare @skip int set @skip = @pageno * @qty - @qty

http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx

ASKER

just comparing examples..
thanks

Yes, mine will only work if the ID's are without gaps ... as in your example.

ASKER

OK Yveau, thanks anyway but I cant take that for granted..
Imitchie,
Just trying to understand here, the @skip variable calculates what the starting id should be for each call, right?
i dont understand what @start = ID is doing though as it doesnt relate to anything that I can see??
thanks


if @pageno > 1
begin
	declare @skip int set @skip = @pageno * @howmany - @howmany
	set rowcount @skip
 
	select			@start = ID 
	from			mytable 
	order by		ID
 
	set rowcount 0
end

Open in new window

Maybe this is acceptable ?
Downside is the 'copying' of all data to the #Temp table ...

Hope this helps ...

create table #Yveau (id int, Descr varchar(10))
go
 
insert into #Yveau values (1,'Aaa')
insert into #Yveau values (2,'Bbb')
insert into #Yveau values (3,'Ccc')
insert into #Yveau values (7,'Ggg')
insert into #Yveau values (8,'Hhh')
insert into #Yveau values (10,'Jjj')
insert into #Yveau values (12,'Lll')
insert into #Yveau values (14,'Nnn')
insert into #Yveau values (20,'Ttt')
go
 
create procedure usp_GetResults
    @pageno int
,   @qty int
as
begin
		select ROW_NUMBER() OVER(ORDER BY ID ASC) as Nr
		,      ID
		,      Descr
		into   #Temp
		from   #Yveau
 
		select  ID, Descr
		from    #Temp
		where   Nr between (((@pageno-1)*@qty)+1) and (@pageno*@qty)
end
go
 
 
--

Open in new window

declare @skip int set @skip = @pageno * @howmany - @howmany
set rowcount @skip
select @start = ID
from mytable
order by ID
set rowcount 0

SQL Server has a habit of assigning every time it encounters a row. So if you have rowcount of 10, and you have @start=ID (ordered by ID), the last assignment will leave it at the 10th. The next part, line 17 "where id > isnull(@start, 0)" will cause it to SKIP up to that last (10th) ID. neat!

ASKER

thanks for your suggestions, i'll have a play with them and let you know. will be able to give proper feedback on monday.
take care in the meantime.

ASKER

Imitchie:
your code snipper works although can you explain what the following means please?

select @start = ID

thanks

I thought I explained it above http:#20385869

ASKER

i have added the appropriate columns into the query when the pageno > 1 although I get the following error message:
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

any ideas?
thanks


SELECT    @start = C.ID,
          rtrim(C.comments) as comments,
          rtrim(M.username) as user,
          rtrim(M.picture) as picture						
FROM      comments as C,
          members as M,
          articles as A
WHERE     C.memberid = M.id
AND       C.videoid = A.id
AND       A.id = @articleid
ORDER BY  C.id DESC

Open in new window

ASKER

not completely sure what you mean but I have changed the following line:

declare @skip int set @skip = @pageno * @howmany

to:

declare @skip int set @skip = @pageno * @howmany - @howmany

ASKER

this is what I have so far for when the pageno > 1

--exec usp_test 1,3,1
 
ALTER proc usp_test 
			@pageno int, 
			@howmany int,
			@testid int
AS
SET nocount ON
DECLARE @start int
 
if @pageno > 1
begin
      declare @skip int set @skip = @pageno * @howmany - @howmany
	  set rowcount @skip
 
		--select @start = ID from mytable order by ID
 
		SELECT			@start = C.ID,
					name,
					address,
					tele,
					postal							
		FROM			creds as C,
					regs as R,
					tests as T
		WHERE			C.id = R.id
		AND			C.testid = T.id
		AND			T.id = @testid
		ORDER BY		C.id DESC
 
 
	  set rowcount 0
end
 
set nocount on
set rowcount @howmany
	
	select		name
	from		mytable
	where		id > isnull(@start, 0)
	order by	ID
 
set rowcount 0

Open in new window

Added @articleid parameter
The entire Select statement must be put between
set rowcount @skip and
set rowcount 0
But, the only field in SELECT must be @start = C.ID
The rest of the query must be put after the last
set rowcount @howmany
Except, add one condition to the SELECt, to make it start after our @start ID.

I notice that you are using c.id DESC, so the condition becomes <@start

create proc usp_GetResults (@pageno int , @howmany int, @articleid int)
as
set nocount on
declare @start int
if @pageno > 1
begin
  declare @skip int set @skip = @pageno * @howmany - @howmany
  set rowcount @skip
SELECT    @start = C.ID
FROM      comments as C,
          members as M,
          articles as A
WHERE     C.memberid = M.id
AND       C.videoid = A.id
AND       A.id = @articleid
ORDER BY  C.id DESC
  set rowcount 0
end
 
set nocount on
set rowcount @howmany
 
SELECT    C.ID,
          rtrim(C.comments) as comments,
          rtrim(M.username) as user,
          rtrim(M.picture) as picture                                           
FROM      comments as C,
          members as M,
          articles as A
WHERE     C.memberid = M.id
AND       C.videoid = A.id
AND       A.id = @articleid
AND       (@start is null or C.id < @start)
ORDER BY  C.id DESC
 
set rowcount 0

Open in new window

ASKER

looking good in sql, let me just try it in my test harness. thanks

ASKER

hi there,
is it possible to return how many C.comments are present for the given articleID using this technique?
thank you.

How about adding a count in there. The pattern is

DECLARE @total int
SELECT @total = count(*)
FROM comments as C,
members as M,
articles as A
WHERE C.memberid = M.id
AND C.videoid = A.id
AND A.id = @articleid

(only Select field is @total=count(*), no ORDER by)
But I don't know what you want to do with the count though. It's in variable @total after the select

create proc usp_GetResults (@pageno int , @howmany int, @articleid int)
as
set nocount on
declare @start int
if @pageno > 1
begin
  declare @skip int set @skip = @pageno * @howmany - @howmany
  set rowcount @skip
SELECT    @start = C.ID
FROM      comments as C,
          members as M,
          articles as A
WHERE     C.memberid = M.id
AND       C.videoid = A.id
AND       A.id = @articleid
ORDER BY  C.id DESC
  set rowcount 0
end
 
DECLARE   @total int
SELECT    @total = count(*)
FROM      comments as C,
          members as M,
          articles as A
WHERE     C.memberid = M.id
AND       C.videoid = A.id
AND       A.id = @articleid
 
set nocount off
set rowcount @howmany
 
SELECT    C.ID,
          rtrim(C.comments) as comments,
          rtrim(M.username) as user,
          rtrim(M.picture) as picture                                           
FROM      comments as C,
          members as M,
          articles as A
WHERE     C.memberid = M.id
AND       C.videoid = A.id
AND       A.id = @articleid
AND       (@start is null or C.id < @start)
ORDER BY  C.id DESC
 
set rowcount 0

Open in new window

ASKER

thats okay, i think I have it working, just need to sort a few things out with it to totally know whether its complete...
let you know,
this is great.

ASKER

just trying to complete my understanding here,

if I want to add another query into this sp, not that I do but just to understand why this works I can simply add the following?

then this will create another cell within the returned table with the name test? or do I have to include the variable within the initial select to return it?

thank you

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[...]  
				@... varchar(100)
AS
 
SELECT		...
FROM		...
WHERE		...
 
 
// added query to return a new value
 
DECLARE		@test int
SELECT		@test = count(articleid)
from		testtable
where		articleid= 1

Open in new window

In your last select, add the variable to the Select list.

SELECT @test as TestCount,
C.ID,
rtrim(C.comments) as comments,
rtrim(M.username) as user,
rtrim(M.picture) as picture
FROM comments as C,
members as M,
articles as A
WHERE C.memberid = M.id
AND C.videoid = A.id
AND A.id = @articleid
AND (@start is null or C.id < @start)
ORDER BY C.id DESC

ASKER

i've tried this (see code snippet) which returns mytotal as null.
but when I run the new part separately like this (immediately below this text) i get a value for mytotal?

SELECT            count(c.articleid)
FROM            comments as c,
              article as a
WHERE            a.id = c.articleid
AND             a.title = 'test'


 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[...]  
				@... varchar(100)
AS
 
DECLARE		@total int
 
SELECT		@total as mytotal,
		...
FROM		article as a,
			members as m,
			rating as r,
			comments as c
WHERE		a.memberid = m.id
AND		a.id = r.videoid
AND		a.id = c.articleid
AND		a.title = @article_name
 
 
// new part
 
SELECT		@total = count(c.articleid)
FROM		comments as c,
		article as a
WHERE		a.id = c.articleid
AND		a.title = @article_name

Open in new window

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

ok i'll try but this is what i've found:

if I use this:

DECLARE            @total int
SELECT            @total = count(c.testid)
FROM             ...
WHERE            ...
select @total as mytotal

this works great, but when I place the exact same code in the following it returns null.

SELECT            @totalcomments,
FROM.........
WHERE...........

DECLARE            @total int
SELECT            @total = count(c.testid)
FROM             ...
WHERE            ...
--select @total as mytotal

the column returns null

SELECT @total = count(...)
puts the count into @total

If you're using @total before you execute the SELECT, then how is it supposed to get a value??

ASKER

this is just trying to understand why it works.
the stored procedure works great for pagination but Im just trying to understand that in an sp you can write something like

select something
from something
where something

--and then write another query

select something else
from something else
where something else

all my sp's just have one query until your comments so im just trying to understand that you can have multiple queries in one sp (and I dont mean sub queries) and how the results can relate to each other, i.e. variables from one result being used in the other part.

is that clear? this isnt really for pagination, im just wanting to understand how multiple queries work together in one sp.

ASKER

ahhhhh.. seems stupidly obvious now but the order matters.. doh

Okay. Variable values flow on from one query to the next.
When you're doing multiple select/insert/update within one stored procedure, it's common to put
set nocount on/off
at the top bottom which suppresses the
x row(s) affected
information messages. Otherwise it can confuse the user or calling program as to which result they really want.

ASKER

great, cheers.