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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-06-08 : 08:05:40
|
| Chad writes "I am trying to write a stored proc which will return a tree structure resultsetI have two tables that I am dealing withEmployee EmpID EmpNameAuth EmpID -> Employee.EmpID AuthID -> Employee.EmpIDIn the Auth table EmpID specifies an EmpID who has the authorization to view AuthID(EmpIDs) informationSo there can be duplcates of EmpID and AuthID but each combo is the primary keyI want to return a resultset that based off an individual EmpID, contains all of his AuthIDs and each of those AuthIDs, AuthIdsin a structure something like this if I passed in the EmpID 'Emp1'EmpID AuthIDEmp1 Emp2Emp2 Emp5Emp2 Emp6Emp2 Emp7Emp1 Emp3Emp3 Emp4I have tried several types of JOINs but the primary problem is I do not know how many layers I could be dealing with so there is no set number of times I know to reJOIN the tables.I tried recursively calling the SP based on a looping a set cursor but apparently defining a cursor in the SP conflicts with subsequent calls to that SPI would like to get this back as a single record set. But if this is not possible I guess I can recursively call the SP from the Web App. I just thought I might see if you had any ideas.Chad Braun" |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-06-08 : 19:20:17
|
| Declare the cursor as LOCAL. That should do it.Check out the following article containing a sample recursive SP using cursors:http://www.sqlteam.com/item.asp?ItemID=8595 |
 |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
|
|
|
|
|
Recursive Stored Procedures 
why not return the paths without a cursor??