how to call function recursively in sql server
Answers
Brian
2232
Points
14
Posts
|
Hi sam, Use following recursive function: CREATE FUNCTION [dbo].[GetAllSubCategoryID]
(
@CategoryID_Arg BIGINT
)
RETURNS @RtnValue TABLE
(
CategoryID BIGINT
)
AS
BEGIN
DECLARE @CategoryID BIGINT
IF EXISTS(SELECT * FROM GS_Category WHERE CategoryParentID=@CategoryID_Arg)
BEGIN
DECLARE TEMPGetAllSubCategoryCURSOR CURSOR STATIC FOR
SELECT CategoryID FROM GS_Category WHERE CategoryParentID=@CategoryID_Arg
OPEN TEMPGetAllSubCategoryCURSOR
FETCH NEXT FROM TEMPGetAllSubCategoryCURSOR INTO @CategoryID
WHILE @@FETCH_STATUS=0
BEGIN
Insert Into @RtnValue(CategoryID)VALUES(@CategoryID)
Insert Into @RtnValue(CategoryID)
SELECT CategoryID FROM dbo.GetAllSubCategoryID(@CategoryID)
FETCH NEXT FROM TEMPGetAllSubCategoryCURSOR INTO @CategoryID
END
CLOSE TEMPGetAllSubCategoryCURSOR
DEALLOCATE TEMPGetAllSubCategoryCURSOR
END
RETURN
END
Above function return all child categoryid as table: select * from dbo.GetAllSubCategoryID(1)
Posted On:
01-Jun-2015 05:12
|
Blog
Active User (0)
No Active User!