Solved

Returning a List of Records using Stored Procedure

Posted on 2006-11-15
5
245 Views
Last Modified: 2010-04-16
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
Comment
Question by:nadermik
  • 2
5 Comments
 
LVL 27

Accepted Solution

by:
MikeToole earned 500 total points
ID: 17949290
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
 

Author Comment

by:nadermik
ID: 17954229
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
 
LVL 27

Expert Comment

by:MikeToole
ID: 17954299
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A short article about problems I had with the new location API and permissions in Marshmallow
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question