Link to home
Create AccountLog in
Avatar of gdspeare
gdspeare

asked on

SQL Cursor

I am working on building a sp to populate a treeview in vb.net and am not very confident about cursors.  Thanks in advance for the help.  Hopefully the code attached is noted well enough.


create table #users_test
(record_number int not null identity
,user_id varchar(255) not null
,user_name varchar(255)
,PID varchar(255)
,Dept_descr varchar(255) not null)
 
Insert into #users_test
Values
(suser_sname(),'Just Me','125587','Medicine-Cardio')
 
Insert into #users_test
Values
(suser_sname(),'Just Me','125588','Medicine-Cardio')
 
Insert into #users_test
Values
(suser_sname(),'Just Me','654321','Medicine-Pulm')
 
Insert into #users_test
Values
(suser_sname(),'Just Me','123456','Medicine-Pulm')
 
 
 
/*
Insert into users_test
Values(
'utmsa\gs2007','David Speare','654321','Medicine-Cardiology')
*/
 
-- Step 1:	Create the tree table
create table #Tree
(rec_num int not null identity
,parent_id int not null
,Display_name varchar(255) not null
,Tag varchar(255) not null
,Level varchar(10) not null
)
 
-- Step 2:	Add the root node displaying user id
Insert into #Tree (Parent_id,Display_name,Tag,Level)
Select distinct '0', user_name,'All','1' from #users_test where user_id = rtrim(suser_sname())
 
 
-- Step 3:	Add the first level
Insert into #Tree (Parent_id,Display_name,Tag,Level)
Select distinct (select rec_num from #tree
				 where parent_id = '0')
		, Dept_descr,Dept_descr,'2' from #users_test where user_id = rtrim(suser_sname())
 
 
-- Step 4:	need to put a loop through users table and get the PIDs where
--			display name = display name for every level 2 record
 
select * from #tree
 
select * from #users_test

Open in new window

Avatar of JestersGrind
JestersGrind
Flag of United States of America image

Why do you need to loop through the tables?  Couldn't you do something like this?

Greg



SELECT PID 
FROM #users_test AS ut INNER JOIN 
     #Tree AS t ON t.Display_name = ut.Dept_descr
WHERE [Level] = 2
 
 
 

Open in new window

Avatar of gdspeare
gdspeare

ASKER

I don't think so.

I have to look at each record in the tree table and find the projects that roll up into that tree based on the users table.
ASKER CERTIFIED SOLUTION
Avatar of JestersGrind
JestersGrind
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Great suggestion.  Thanks