Solved

Returning a List of Records using Stored Procedure

Posted on 2006-11-15
5
246 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Suggested Solutions

Title # Comments Views Activity
Advice in Xamarin 21 121
what is the best Integrated development environment 2 66
Why use this lambda? 12 96
COM server issue 2 28
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

751 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