troubleshooting Question

SQL Cursor

Avatar of gdspeare
gdspeare asked on
SQL
4 Comments1 Solution313 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
JestersGrind

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros