create table tblCategories ( cID int, cName varchar(20), cParentID int ) insert tblCategories(cID, cName, cParentID) values (1, 'Computers', 0) insert tblCategories(cID, cName, cParentID) values (2, 'Hardware', 1) insert tblCategories(cID, cName, cParentID) values (3, 'Sound Cards', 2) insert tblCategories(cID, cName, cParentID) values (5, 'Creative Labs', 3) go create function YouAreHere( @cID int ) returns varchar(300) as begin return '' end go alter function YouAreHere( @cID int ) returns varchar(300) as begin if @cID = 0 return '' declare @cName varchar(20) declare @cParentID int select @cName = cName, @cParentID = cParentID from tblCategories where cID = @cID return dbo.YouAreHere(@cParentID) + ' / ' + @cName end go create procedure pYouAreHere( @cID int, @YAH varchar(300) output ) as set @YAH = '' go alter procedure pYouAreHere( @cID int, @YAH varchar(300) output ) as set @YAH = NULL if @cID = 0 begin set @YAH = '' return end declare @cName varchar(20) declare @cParentID int declare @YAHp varchar(300) select @cName = cName, @cParentID = cParentID from tblCategories where cID = @cID if @cParentID is null return exec pYouAreHere @cParentID, @YAHp output set @YAH = @YAHp + ' / ' + @cName go select cID, dbo.YouAreHere(cID) as YouAreHere from tblCategories go declare @i int, @m int declare @YAH varchar(300) set @m = (select max(cID) from tblCategories) set @i = 1 while @i <= @m begin exec pYouAreHere @i, @YAH output select @i as cID, @YAH as YouAreHere set @i = @i + 1 end go drop table tblCategories drop function YouAreHere drop procedure pYouAreHere