Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

Returning a List of Records using Stored Procedure

Hi

i want to build a stored procedure in SQL 2000 to give me list of records in the following scenario

i have a table which has the employee id and the manager id and there could be one of the managers is a manager of another set of employees and so on

so i want to bring list of the employees who are reporting to certain manager and all the employees they are reporting to them and so on
i want to get that by passing the manager id

and i want to use that for reporting purposes

can you please help me to do that

regards
0
nadermik
Asked:
nadermik
  • 2
1 Solution
 
MikeTooleCommented:
This procedure sholud point the way:

alter procedure EmployeeList
@id as int
as
begin
  create table #Emps(id integer)
  insert #Emps select @id

  while @@RowCOunt > 0
      insert #Emps select W.empID from employee W inner join #Emps on #Emps.id = w.MgrID
      where empid not in (select id from #emps)
  select E.* from employee E inner join #emps on E.empid = #emps.id
end

The assumption is an Employee table with EmpID and MgrID as the employee and Manager IDs.

It creates a temporary table to store the IDs that need to be returned, inserts the ID supplied as a parameter, then loops round to insert the employees for the managers already selected. The loop ends when there's a zero record count from the insert.
It then returns the employee table data for the IDs selected
0
 
nadermikAuthor Commented:
Thank You for your help

i wonder if you can guide me further how to take the output results of the emp id from the stored procedure to be filter of records in a crystal report
regards
nader
0
 
MikeTooleCommented:
You should submit this as a new question in the Crystal Reports subsection of the Databases topic where there are Experts who can answer it - I have no experience in that area.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now