Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1155
  • Last Modified:

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

0
jimbona27
Asked:
jimbona27
  • 17
  • 10
  • 3
  • +1
1 Solution
 
YveauCommented:
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
 
jimbona27Author Commented:
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
 
imitchieCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
imitchieCommented:
oops... please replace line 7:

  declare @skip int set @skip = @pageno * @qty - @qty
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
jimbona27Author Commented:
just comparing examples..
thanks
0
 
YveauCommented:
Yes, mine will only work if the ID's are without gaps ... as in your example.
0
 
jimbona27Author Commented:
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
 
YveauCommented:
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
 
imitchieCommented:
       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
 
jimbona27Author Commented:
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
 
jimbona27Author Commented:
Imitchie:
your code snipper works although can you explain what the following means please?

select            @start = ID

thanks
0
 
imitchieCommented:
I thought I explained it above http:#20385869
0
 
jimbona27Author Commented:
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
 
jimbona27Author Commented:
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
 
jimbona27Author Commented:
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
 
imitchieCommented:
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
 
jimbona27Author Commented:
looking good in sql, let me just try it in my test harness. thanks
0
 
jimbona27Author Commented:
hi there,
is it possible to return how many C.comments are present for the given articleID using this technique?
thank you.
0
 
imitchieCommented:
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
 
jimbona27Author Commented:
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
 
jimbona27Author Commented:
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
 
imitchieCommented:
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
 
jimbona27Author Commented:
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
 
imitchieCommented:
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
 
jimbona27Author Commented:
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
 
imitchieCommented:
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
 
jimbona27Author Commented:
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
 
jimbona27Author Commented:
ahhhhh.. seems stupidly obvious now but the order matters.. doh
0
 
imitchieCommented:
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
 
jimbona27Author Commented:
great, cheers.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

  • 17
  • 10
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now