Child Parent relation in c#.net & SQL Server 2008

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

SQL Server 2008 Forums

Transact-SQL (2008)

Child Parent relation in c#.net & SQL Server 2008

Author

Topic

Preeti_Mumbai
Starting Member

4 Posts

Posted - 2011-08-04 : 05:36:01

hi all,

i am using CTE, but failed to use with SP

;WITH EmpCTE AS
(
SELECT a.EmployeeId,CAST ('' AS VARCHAR(500)) AS Reporting_Heads
FROM EmpTable a
WHERE
a.BossId IS NULL

UNION ALL

SELECT E.EmployeeId,
CAST (Ec.reporting_Heads + ',' + CAST(Ec.EmployeeId AS Varchar(200)) AS VARCHAR(500)) AS Reporting_Heads

FROM EmpCTE Ec
INNER JOIN
EmpTable E
ON
E.BossId = Ec.EmployeeId
)

SELECT Reporting_Heads FROM EmpCTE WHERE EmpCTE.EmployeeId = 100004

i am having emp table.

i want to find out Emp BossID,

i want to pass EmployeeId Through Store Procedure, which i will be using in C#.net and want result like below mention screen.

[url]http://postimage.org/image/1ill0o0bo/[/url]

Thanks

Preeti

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-04 : 06:02:21

[code]
declare @EmpID int

select @EmpID = 100004

; with
rcte as
(
select FullName, EmployeeID, BossID, lvl = 1
from EmpTable
where EmployeeID = @EmpID

union all

select e.FullName, e.EmployeeID, e.BossID, lvl = r.lvl + 1
from EmpTable e
inner join rcte r on e.EmployeeID = r.BossID
)
select BossID
from rcte
order by lvl desc
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Preeti_Mumbai
Starting Member

4 Posts

Posted - 2011-08-04 : 06:36:12

thanks for your quick response dear,
your solution is working very fine, only one problem,
i don`t want null value in result set.
result after your solution executed-:
NULL
100000
100001
100002
100003
but i want result only below mention-:
100000
100001
100002
100003

thanks

quote:

Originally posted by khtan


declare	@EmpID	int

select	@EmpID	= 100004

; with 
rcte as
(
	select	FullName, EmployeeID, BossID, lvl = 1
	from	EmpTable
	where	EmployeeID	= @EmpID

	union all

	select	e.FullName, e.EmployeeID, e.BossID, lvl = r.lvl + 1
	from	EmpTable e
		inner join rcte	r on	e.EmployeeID	= r.BossID
)
select	BossID
from	rcte
order by lvl desc

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-04 : 07:44:10

just add a WHERE clause and check for NULL


where	BossID	is not null

KH
[spoiler]Time is always against us[/spoiler]

Preeti_Mumbai
Starting Member

4 Posts

Posted - 2011-08-04 : 07:50:43

done it,

thank you so much for your help.

Thanks

quote:
Originally posted by khtan

just add a WHERE clause and check for NULL

where BossID is not null

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources