I am pretty new to stored procedures, so any help will be highly appreciated.
We need to get a list of subordinate position ids from a table by specifying a 'manager' position id. This is the approach I have taken.
1: Create a temp. table (this is a real table & will get deleted at the end).
2: Get position ids of employees whose manager position id is the specified id, and then write to the above temp. table.
3: For each of the position id's returned above, call the procedure at 2 & repeat this until the returned result set is empty.
4: Finally execute a SELECT * ... on the temp table & the resultset gets returned.
All but step 4 works fine. I get the result in the temp. table but the procedure returns an empty record set.
Pasted below is the code. I am not sure wat I am doing wrong.