Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

stored procedure help to return paginated results

Posted on 2007-11-29
31
Medium Priority
?
1,151 Views
Last Modified: 2009-07-29
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

0
Comment
Question by:jimbona27
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 17
  • 10
  • 3
  • +1
31 Comments
 
LVL 18

Expert Comment

by:Yveau
ID: 20372556
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

0
 

Author Comment

by:jimbona27
ID: 20372611
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
...

??
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20372684
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

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 25

Expert Comment

by:imitchie
ID: 20372695
oops... please replace line 7:

  declare @skip int set @skip = @pageno * @qty - @qty
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20372737
0
 

Author Comment

by:jimbona27
ID: 20372982
just comparing examples..
thanks
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20373242
Yes, mine will only work if the ID's are without gaps ... as in your example.
0
 

Author Comment

by:jimbona27
ID: 20380822
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

0
 
LVL 18

Expert Comment

by:Yveau
ID: 20381461
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

0
 
LVL 25

Expert Comment

by:imitchie
ID: 20385869
       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!
0
 

Author Comment

by:jimbona27
ID: 20388136
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.
0
 

Author Comment

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

select            @start = ID

thanks
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20394570
I thought I explained it above http:#20385869
0
 

Author Comment

by:jimbona27
ID: 20394589
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

0
 

Author Comment

by:jimbona27
ID: 20394600
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
0
 

Author Comment

by:jimbona27
ID: 20394616
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

0
 
LVL 25

Expert Comment

by:imitchie
ID: 20394622
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

0
 

Author Comment

by:jimbona27
ID: 20394683
looking good in sql, let me just try it in my test harness. thanks
0
 

Author Comment

by:jimbona27
ID: 20394794
hi there,
is it possible to return how many C.comments are present for the given articleID using this technique?
thank you.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20394810
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

0
 

Author Comment

by:jimbona27
ID: 20394894
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.
0
 

Author Comment

by:jimbona27
ID: 20401924
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

0
 
LVL 25

Expert Comment

by:imitchie
ID: 20401946
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
0
 

Author Comment

by:jimbona27
ID: 20402025
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

0
 
LVL 25

Accepted Solution

by:
imitchie earned 2000 total points
ID: 20402066
let's try this again.
If you DO NOT order by DESC, then the last 4 lines should be

AND       (@start is null or <ID column> > @start)
ORDER BY  <ID column>
 
set rowcount 0
create proc usp_GetResults (
  @pageno int ,
  @howmany int,
  .... additional params)
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 column>
  FROM      ...<all tables>
  WHERE     ...<include ALL criteria>
  ORDER BY  <ID column> [DESC]   -- desc if required
  set rowcount 0
end
 
DECLARE   @total int
SELECT    @total = count(*)
FROM      ....<all tables>
WHERE     ...<include ALL criteria>
 
set nocount off
set rowcount @howmany
 
SELECT    @total as mytotal,
          ...my other fields...
FROM      ...<all tables>
WHERE     ...<include ALL criteria>
AND       (@start is null or <ID column> < @start)
ORDER BY  <ID column> DESC
 
set rowcount 0

Open in new window

0
 

Author Comment

by:jimbona27
ID: 20402084
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
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20402088
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??
0
 

Author Comment

by:jimbona27
ID: 20402098
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.

0
 

Author Comment

by:jimbona27
ID: 20402102
ahhhhh.. seems stupidly obvious now but the order matters.. doh
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20402108
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.
0
 

Author Comment

by:jimbona27
ID: 20402287
great, cheers.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question