jimbona27
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
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
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
...
??
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
oops... please replace line 7:
declare @skip int set @skip = @pageno * @qty - @qty
declare @skip int set @skip = @pageno * @qty - @qty
ASKER
just comparing examples..
thanks
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
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
Maybe this is acceptable ?
Downside is the 'copying' of all data to the #Temp table ...
Hope this helps ...
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
--
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!
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.
take care in the meantime.
ASKER
Imitchie:
your code snipper works although can you explain what the following means please?
select @start = ID
thanks
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
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
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
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
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
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
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.
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
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
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.
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
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
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
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'
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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??
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.
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.
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.
Hope this helps ...
Open in new window