[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Returning a List of Records using Stored Procedure

Posted on 2006-11-15
5
Medium Priority
?
264 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
3 Comments
 
LVL 27

Accepted Solution

by:
MikeToole earned 2000 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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
Simple Linear Regression
Introduction to Processes

611 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