Solved

stored procedure help to return paginated results

Posted on 2007-11-29
31
1,100 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
  • 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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 500 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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now