You need to look at using a recursive CTE to traverse the Hierachy with a bit tweaking of the example in BoL (http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx)

This should give you a good starting point

CREATE TABLE [#Employee](

[EmpID] [int] NOT NULL,

[EmpName] [varchar](50) NOT NULL,

[SupID] [int] NULL)

GO

INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (1, 'ram', 2)

INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (2, 'sureh', 3)

INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (3, 'vimal', null)

DECLARE @Emp varchar(50) = 'ram'

;WITH CTE_Traverse_hierarchy

AS

(

--Anchor

SELECT EmpId,EmpName,SupId,0 Lvl

FROM #Employee

Where EmpName=@Emp

UNION ALL

SELECT

E.EmpId,E.EmpName,E.SupId,Lvl+1 Lvl

FROM #Employee E

JOIN CTE_Traverse_hierarchy Parent on Parent.SupId=E.EmpId

)

Select *

from

CTE_Traverse_hierarchy

This will give you all the parents for a given Employee. After this its quite simple to move up the hierarchy.