how to call function recursively in sql server
|
sam
378
Points
48
Posts
|
Hi, I have table Category as:
I want all child CategoryID of given CategoryID=1 as:
How can I find by recursive function in sql server?
Views:
8715
Total Answered: 1 Total Marked As Answer: 1 Posted On: 01-Jun-2015 07:55 Share: 
|
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
|
