How do I get prev/current/next record (no cursor)?
-
I have a sproc to get previous, current, next records. It has a parameter to get more than one previous\next records.
I am doing it with CURSORS and temp tables. But, I have read they could impact server performance and that it is advisable to avoid using ´em.
How can I do the same using regular SELECT?
-
It'd be much easier to provide a solution if you post your sproc...all depends on your parameter, query etc...for example, if your parameter is an ID, you could so something like:
"select max(id) from ... where max(id) @parameter" - for the next row
..you should also read the article on "cursors" published on today's ssc - actually, more than the article itself, the ensuing comments/discussion would be of more help...
**ASCII stupid question, get a stupid ANSI !!!** -
OK, below is a sproc similar to what I was referring to. This one doesn´t use temp tables. When I use temp tables what I do is I put every fetched record in a temp table.
***
If I use this:
"select max(id) from ... where max(id) @parameter" - for the next row
I think I cannot display several columns. Can I?
***
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetTabNavigation]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetTabNavigation]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE procedure dbo.GetTabNavigation
@TabId int
as
--THESE ARE PRIOR PAGES
DECLARE prior_cursor CURSOR SCROLL FOR
select top 3
TabID, TabOrder, TabName
from Tabs
where TabId @TabId
order by tabid asc, taborder asc
OPEN next_cursor
-- Perform the first fetch.
FETCH FIRST FROM next_cursor
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- execute as long as the initial fetch succeeded
FETCH NEXT FROM next_cursor
END
CLOSE next_cursor
DEALLOCATE next_cursor
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
If I use this:
"select max(id) from ... where max(id) < @parameter" - for the previous row &
"select min(id) from ... where min(id) > @parameter" - for the next row
I think I cannot display several columns. Can I?
you can inner join them back to your original query
select B.field1,B2.field2
from (select max(id) AS maximumid from ....) A
inner join (id, field1,field2 from ....) B
on A.maximumid =B.id
-
sorry for asking what may be a stupid question - are you sure you meant columns and not rows... ?!?!
Another alternative to get all your rows is to store the min & max id in variables and then perform the query...w/out variables would be something like this...
select TabID, TabOrder, TabName from Tabs where TabId between (select min(TabId) from Tabs where TabId in (select top 3 TabId from Tabs where TabId @TabId order by TabId)) order by TabId
**ASCII stupid question, get a stupid ANSI !!!** -
Hello,
I don't see anything wrong with the cursors when you have to access the previous /current/next record. The performance issues may be also if you put too much processing into the WHERE.
Regards,Yelena Varsha
-
Yes sushila I meant columns. I thought I posted my solution giving you and Jo credits and thanks.
Here is the complete code for a Previous, Current and Next. If I had to get more than one previous or next record then Min and Max would not help. That is something the CURSOR can handle easily using SELECT TOP n .
Well, yes Yelena, CURSORS are a problem in my situation where there are hundreds of users executing the sproc. It is for a portal comprised of several thousands of pages and for each page I want to dinamically fetch previous and next page. And, you know...maybe using UNION will impact performance, and I really do not know how much when compared to using CURSORS.
/* Assuming we are on TabID = 52 */
select
'Previous',
B.TabID,
B.TabOrder,
B.TabName
from Tabs B
inner join (select max(TabID) as MaxID from Tabs where TabID < 52) A
on B.TabID = A.MaxID
UNION
select
'Current',
TabID,
TabOrder,
TabName
from Tabs
where TabID = 52
UNION
select
'Next',
B.TabID,
B.TabOrder,
B.TabName
from Tabs B
inner join (select min(TabID) as MaxID from Tabs where TabID > 52) A
on B.TabID = A.MaxID
Order By TabID
/* End of Select */
-
I assume that the TabID is not sequential, otherwise you just could use BETWEEN. Can you create a surrogate key that is sequential?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply