-----------------------------------------------------------

New Message on MumbaiUserGroup

-----------------------------------------------------------
From: Harshal_13071
Message 2 in Discussion


Hello santoshjust try this and check how it helps youbyeHarshal ShahCREATE TABLE 
dbo.Emp<o:p></o:p>(<o:p></o:p>            EmpID                      int            
PRIMARY KEY,<o:p></o:p>            EmpName                 varchar(30),<o:p></o:p>     
       MgrID                      int            FOREIGN KEY REFERENCES 
Emp(EmpID)<o:p></o:p>)<o:p></o:p>GO<o:p></o:p>

 <o:p></o:p>INSERT dbo.Emp SELECT 1, 'President', NULL<o:p></o:p>INSERT dbo.Emp SELECT 
2, 'Vice President', 1<o:p></o:p>INSERT dbo.Emp SELECT 3, 'CEO', 2<o:p></o:p>INSERT 
dbo.Emp SELECT 4, 'CTO', 2<o:p></o:p>INSERT dbo.Emp SELECT 5, 'Group Project Manager', 
4<o:p></o:p>INSERT dbo.Emp SELECT 6, 'Project Manager 1', 5<o:p></o:p>INSERT dbo.Emp 
SELECT 7, 'Project Manager 2',
 5<o:p></o:p>INSERT dbo.Emp SELECT 8, 'Team Leader 1', 6<o:p></o:p>INSERT dbo.Emp 
SELECT 9, 'Software Engineer 1', 8<o:p></o:p>INSERT dbo.Emp SELECT 10, 'Software 
Engineer 2', 8<o:p></o:p>INSERT dbo.Emp SELECT 11, 'Test Lead 1', 6<o:p></o:p>INSERT 
dbo.Emp SELECT 12, 'Tester 1', 11<o:p></o:p>INSERT dbo.Emp SELECT 13, 'Tester 2', 
11<o:p></o:p>INSERT dbo.Emp SELECT 14, 'Team Leader 2', 7<o:p></o:p>INSERT dbo.Emp 
SELECT 15, 'Software Engineer 3',
 14<o:p></o:p>INSERT dbo.Emp SELECT 16, 'Software Engineer 4', 14<o:p></o:p>INSERT 
dbo.Emp SELECT 17, 'Test Lead 2', 7<o:p></o:p>INSERT dbo.Emp SELECT 18, 'Tester 3', 
17<o:p></o:p>INSERT dbo.Emp SELECT 19, 'Tester 4', 17<o:p></o:p>INSERT dbo.Emp SELECT 
20, 'Tester 5', 17<o:p></o:p>GO<o:p></o:p>

 <o:p></o:p>CREATE PROC dbo.ShowHierarchy<o:p></o:p>(<o:p></o:p>            @Root 
int<o:p></o:p>)<o:p></o:p>AS<o:p></o:p>BEGIN<o:p></o:p>            SET NOCOUNT 
ON<o:p></o:p>            DECLARE @EmpID int, @EmpName varchar(30)<o:p></o:p> 
<o:p></o:p>            SET @EmpName = (SELECT EmpName FROM dbo.Emp WHERE EmpID = 
@Root)<o:p></o:p>            PRINT REPLICATE('-', @@NESTLEVEL * 4) + 
@EmpName<o:p></o:p> <o:p></o:p>            SET @EmpID = (SELECT MIN(EmpID) FROM 
dbo.Emp WHERE MgrID = @Root)<o:p></o:p> <o:p></o:p>            WHILE @EmpID IS NOT 
NULL<o:p></o:p>            BEGIN<o:p></o:p>                        EXEC 
dbo.ShowHierarchy @EmpID<o:p></o:p>                        SET @EmpID = (SELECT 
MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root AND EmpID > @EmpID)<o:p></o:p>            
END<o:p></o:p>END<o:p></o:p>GO<o:p></o:p>

 <o:p></o:p>

SMS using the Yahoo! 
Messenger;

Download latest version.

-----------------------------------------------------------

To stop getting this e-mail, or change how often it arrives, go to your E-mail 
Settings.
http://groups.msn.com/MumbaiUserGroup/_emailsettings.msnw

Need help? If you've forgotten your password, please go to Passport Member Services.
http://groups.msn.com/_passportredir.msnw?ppmprop=help

For other questions or feedback, go to our Contact Us page.
http://groups.msn.com/contact

If you do not want to receive future e-mail from this MSN group, or if you received 
this message by mistake, please click the "Remove" link below. On the pre-addressed 
e-mail message that opens, simply click "Send". Your e-mail address will be deleted 
from this group's mailing list.
mailto:[EMAIL PROTECTED]

Reply via email to