Wednesday, October 7, 2009

Recursive function in sql server

Shout it kick it on DotNetKicks.com
alter
FUNCTION dbo.GetParents

(
@id int,@parents varchar(max))

RETURNS
varchar(max)

AS

BEGIN

declare
@itemPkey int

IF EXISTS (SELECT LKP_LKP_PKEY_Level_0 FROM dbo.APP_LOOKUP_TABLE WHERE

LKP_TYP_PKEY
= @id and LKP_LKP_PKEY_Level_0 is not null)

BEGIN

SELECT top 1 @itemPkey=LKP_LKP_PKEY_Level_0 FROM dbo.APP_LOOKUP_TABLE WHERE

LKP_TYP_PKEY
= @id and LKP_LKP_PKEY_Level_0 is not null

set @parents = @parents +',' + (SELECT cast(LKP_TYP_PKEY as varchar(10)) FROM dbo.APP_LOOKUP_TABLE where LKP_PKEY=@itemPkey)

SELECT @parents = dbo.GetParents(LKP_TYP_PKEY, @parents) FROM dbo.APP_LOOKUP_TABLE where LKP_PKEY=@itemPkey

END

RETURN @parents

END

No comments:

Post a Comment