Should I use Cursor ? - 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)

Should I use Cursor ?

Author

Topic

ralph_jj22022
Starting Member

4 Posts

Posted - 2006-07-18 : 03:42:10

Hi Gurus,

I am working on a SQL project and am facing with an issue. I have 6 tables and have a main table (School, say). Now I want to retrieve studentid from this table and on the basis of each studentid find out whether that student exists in the remaining 5 tables. I have been advised to use a cursor so as to fetch each studentid value and then compare this studentid row by row with each of five tables. Is using a cursor the only solution or a better solution can be acheived.

Some thoughts on what I am trying to acheive is:

declare @tempstudentid varchar(50)
declare @studentTemp varchar(100)
declare curStudent cursor for select studentid from school
open curStudent
fetch next from curStudent into @studentTemp

select @tempstudentid=studentid from school where studentid in(select studentGrade, Loccd from Class where studentid=@studentTemp) and studentid not in (select fId, fGrade from faculty where studentid=@studentTemp) and studentid in (select * from Grade where studentid=@studentTemp)....

print @tempstudentid
fetch next from curStudent into @studentTemp
close curStudent
deallocate curStudent

Happy coding,
Sachin

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-18 : 03:52:11

if exists(select * from table1 t1 join table2 t2 on t1.id=t2.id
join table3 t3 on t1.id=t3.id... where id=@studentID)

if you get a record then the ID exists in atleast 1/n tables

--------------------
keeping it simple...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-18 : 03:58:03

or if there is no join key as shown in the jen's solution, you can try....

if  exists(select 1 from table1 where id = @StudentID
           union all select 1 from table2 where id = @StudentID
           .....)

Harsh Athalye
India.
"Nothing is Impossible"

ralph_jj22022
Starting Member

4 Posts

Posted - 2006-07-18 : 04:39:21

Thanks for the replies

But as I mentioned earlier, I have to find whether a record should exist in first table but not in the second table , maybe third table it should not exist but might exist in fifth table...something like:

select * from table1 where @studentid in (firsttable) and @studentid not in (secondtable)...

Regards,
Sachin

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-18 : 04:50:31

>> I am working on a SQL project and am facing with an issue. I have 6 tables and have a main table (School, say). Now I want to retrieve studentid from this table and on the basis of each studentid find out whether that student exists in the remaining 5 tables. I have been advised to use a cursor so as to fetch each studentid value and then compare this studentid row by row with each of five tables. Is using a cursor the only solution or a better solution can be acheived.

lol - who advised that?
How many studentid's are you dealing with and how many days for it to complete?

create table #a (studentid int primary key, tbl1 int not null default 0, tbl2 int default 0, tbl3 int default 0, tbl4 int default 0, tbl5 int default 0, tbl6 int default 0)
insert #a (studentid) select distinct studentid from school

update #a
set tbl1 = 1
from #a
join (select distinct studentid from tbl1) t
on #a.studentid = t.studentid
update #a
set tbl2 = 1
from #a
join (select distinct studentid from tbl2) t
on #a.studentid = t.studentid
.....

That should be ok depending on your data sizes and distribution and the server - you might need to make some changes.

It will give which students exist in which tables. You can delete rows in #a after each step if they don't exist in a table but I would leave that in case you need the info later.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources