please help meout with storeprocedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

please help meout with storeprocedure

Author

Topic

ameya_amu
Starting Member

25 Posts

Posted - 2009-04-16 : 13:25:24

i have following table

empid empname mgrid
1 a 0
2 B 1
3 C 1
4 D 2
5 E 2
6 F 3
7 G 3
8 H 4
9 I 4
10 J 5
11 K 5
12 L 6
13 M 6
14 N 7
15 O 7
16 P 8
17 Q 8
18 R 9
19 S 9
20 T 10
21 U 10
22 V 11
23 W 11
24 X 12
25 Y 12

i want a store procedure that will accept one parameter empid and will give hierarchy of above manager

eg.
if i gave 25 as paramenter
it should give me
12
6
3
1
0

or if i gave 18 as parameter
it should give me
9
4
2
1
0

in descending order only.

please help me out..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-16 : 13:32:35

[code]
;With Emp_Hierarchy (EmpID)
AS
(SELECT mgrid
FROM Table
WHERE empid=@EmpID
UNION ALL
SELECT t.mgrid
FROM Emp_Hierarchy h
JOIN Table t
ON t.empid=h.EmpID
)

SELECT EmpID
FROM Emp_Hierarchy

OPTION (MAXRECURSION 0)
[/code]

ameya_amu
Starting Member

25 Posts

Posted - 2009-04-18 : 11:53:34

its not working, i have only one table with three column. plz help me out

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-18 : 14:48:42

i've also used only single table to get result. I've used CTE to get data recursively.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-18 : 14:55:35

Visakh, one of the columns should be mgrid, right?

quote:
ON t.empid=h.EmpID

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-18 : 14:59:16

quote:
Originally posted by Peso

Visakh, one of the columns should be mgrid, right?
quote:
ON t.empid=h.EmpID

E 12°55'05.63"
N 56°04'39.26"

Nope the CTE has only one column which is EmpID alone. it contain id of manager of passed on employee in begining. then we keep on recursively look for managers employee record and get his manager

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-04-19 : 00:56:49

Here is a Solution for you i do as same as Vishak Sir exactly so its just Vishak Sir Query but here i just change one thing to make you understand that,,, Here

-- Declare Recursive CTE
;with descedent (Mgrid)
AS
(select mgrid from
Vishal1 where empid=25
-- Here you initialize your query
union all
select T.mgrid from descedent D
join Vishal1 T
on t.empid=D.mgrID
-- Here is Recursive Part Of Query which Recurse through all the data
)
SELECT Mgrid
FROM descedent
OPTION (MAXRECURSION 0)

So in this query you just start with query which fetch managerid associated with empid 25 It gives you result 12 and store in CTE(mgrid) so now just recursive part of query do all the things...

If you want more clarification on that so just,,
Serach for "Recursive CTE in Google"

Thanks...
and again its only a repetation of vishak's sir query jus for make u understand
Thanks..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-19 : 03:14:21

quote:
Originally posted by visakh16

Nope the CTE has only one column which is EmpID alone. it contain id of manager of passed on employee in begining. then we keep on recursively look for managers employee record and get his manager

The other column...

quote:
ON t.mgrid=h.EmpID

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources