quote:

---

Originally posted by shepheml

I need to create a stored proc.

---

quote:

---

that returns all contents of one table, but I also need to pass the identity of each row to another stored proc.

---

quote:

---

that will return a value to be included in the result set of the first stored proc.

---

select	[ident]
	,[parent_ident]
	,root_ident = parent_ident
	,[description]
	,[is_company]
into	#temp
from	organization

--one iteration for each nesting level
while @@rowcount > 0
begin
	update a set
		a.root_ident = b.parent_ident
	from	#temp a
	join	#temp b
			on a.root_ident = b.ident
	where	b.Parent_ident <> a.parent_ident
	and	a.root_ident <> b.parent_ident
end

select	[ident]
	,[parent_ident]
	,root_ident
	,[description]
	,[is_company]
from	 #temp

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myOrg99(ident int, ParentId int, [Description] varchar(25))
GO

INSERT INTO myOrg99(ident, ParentId, [Description])
SELECT 1, 1, 'Org_A' UNION ALL
SELECT 2, 2, 'Org_F' UNION ALL
SELECT 3, 1, 'Org_B' UNION ALL
SELECT 4, 2, 'Org_C' UNION ALL
SELECT 5, 3, 'Org_D' UNION ALL
SELECT 6, 4, 'Org_E'
GO

SELECT * FROM myOrg99
GO

CREATE FUNCTION udf_FindTopOrg
	(@ident int)
RETURNS int
AS
  BEGIN
-- [D'oh]
--	SET @Ident = 5
-- [/D'oh]
	DECLARE @ParentID int, @ident2 int
	SELECT @ParentID = 1, @ident2 = 0
	WHILE @ident2 <> @ParentID
	  BEGIN
		SELECT @ParentID = ParentID, @Ident2 = ident 
		  FROM myOrg99 
		 WHERE ident = @ident
		SET @ident = @ParentID
	  END
  	RETURN @Ident2
  END
GO

SELECT ident, ParentId, [Description], dbo.udf_FindTopOrg(ident) AS TopOrg
FROM myOrg99
GO

DROP FUNCTION udf_FindTopOrg
DROP TABLE myOrg99
GO

SELECT	C.ident, C.ParentId, C.[Description], dbo.udf_FindTopOrg(C.ident) AS TopOrg,
	COALESCE(GGGP.ident, GGP.ident, GP.ident, P.ident, C.ident, -1) as RootID
FROM	myOrg99 C
	LEFT OUTER JOIN myOrg99 P
		ON P.ident = C.ParentID
	LEFT OUTER JOIN myOrg99 GP
		ON GP.ident = P.ParentID
	LEFT OUTER JOIN myOrg99 GGP
		ON GGP.ident = GP.ParentID
	LEFT OUTER JOIN myOrg99 GGGP
		ON GGGP.ident = GGP.ParentID
GO