Possible without cursors? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Possible without cursors?

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-03-06 : 23:08:05

Hi All,

I have a table like this


Qid     AnsId	pagebreak
1	1	0
1	2	0
2	3	1
2	5	1
3	9	1
4	12	0
4	13	0
5	16	0
5	17	0
6	20	1

I want to get the records based on the Qid I pass, and may be one more variable to determine forward or backward.
Lets say if pass the Qid as 1,variable as forward then I want to get the records of 1 and 2.


Qid     AnsId	pagebreak
1	1	0
1	2	0
2	3	1
2	5	1

If I pass 2 and say forward I want only the record of 2, since 2nd has pagebreak = 1, so for 4 and forward it would be the records of 4,5 and 6. Basically if its forward then the records which start with passed parameter to the records where the first page break = 1(inclusive). For backward it the reverse process, for 6 and backward I want the record of 6,5,4.

I guess I can use Cursors for this, but any other way without cursors?


create table #test (Qid int,AnsId int,pagebreak tinyint)
insert into #test
select 1,1,0
union
select 1,2,0
union
select 2,3,1
union 
select 2,5,1
union 
select 3,9,1
union 
select 4,12,0
union
select 4,13,0
union 
select 5,16,0
union
select 5,17,0
union
select 6,20,1

Thanks

Karunakaran

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-06 : 23:56:04

Here is something for you to work on. The forward & reverse should be fine by itself. Just need to merge the 2 script into 1. If not possible, just use IF ... ELSE to do it.

declare
	@Qid	int,
	@Dir	int

select	@Qid	= 6, @Dir = 2 -- 1 forward, 2 reverse

-- Forward
select 	*
from	#test t
where	Qid	>= @Qid
and	Qid	<= (select top 1 Qid from #test x where pagebreak = 1 and Qid >= @Qid order by Qid)
order by  case when @Dir = 1 then Qid else -1 * Qid end, AnsId

-- Reversed
select 	*
from	#test t
where	Qid	<= @Qid
and	Qid	> (select top 1 Qid from #test x where pagebreak = 1 and Qid < @Qid order by Qid desc)
order by  case when @Dir = 1 then Qid else -1 * Qid end, AnsId

----------------------------------
'KH'

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-03-07 : 00:18:32

I have to do bit more tweaking to get this working the way I want.
But it gives me the starting point.

Thanks KH.

Karunakaran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources