Function issues - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Function issues

Author

Topic

Xiez
Starting Member

13 Posts

Posted - 2010-03-18 : 10:02:01

Well I'm trying to create a hierarchy type deal. Our tables have a parent ID to state who the section falls under. I need to return a table with every section up to the top. This is my function. It seems to be something with the while loop. It's not looping, and I'm only getting the section that I specify and nothing higher.

ALTER FUNCTION [dbo].[getAgentParents]
(
@agency INT
)
RETURNS @retTable TABLE (AgencyID INT)
AS
BEGIN
DECLARE @parent INT
SET @parent = (SELECT ParentAgencyID FROM Agencies WHERE AgencyID = @agency)
WHILE @parent <> 0
BEGIN
	INSERT INTO @retTable VALUES (@agency)
	SET @agency = @parent
	SET @parent = (SELECT ParentAgencyID FROM Agencies WHERE AgencyID = @agency)
END
Return
END

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-03-18 : 11:23:34

Could you provide some sample data and the expected return value for the sample?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 12:28:51

are you using sql 2005?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources