Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Returning a List of Records using Stored Procedure

Posted on 2006-11-15
5
Medium Priority
?
250 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 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

Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

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
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
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 seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

721 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