my store procedure that call back itself doesn't seems to work.

I have written a store procedure that will delete the record and any children record of it. That store procedure is suppose to call back itself to for every children record, but it only seems to delete the first record (the parent record). Below is my store procedure, pls help.

CREATE PROCEDURE [deleteCMSPage]
(@.PROJ_CMS_PAGE_ID [int])

AS

/* do not delete page id 1 */
if @.PROJ_CMS_PAGE_ID <> 1
begin
-- For storing the list of child activities.
DECLARE @.page_list_cursor CURSOR
DECLARE @.CHILD_ID INTEGER
/* get the children of this page, for every child, call deleteCMSPage */
SET @.page_list_cursor = CURSOR FOR
SELECT PROJ_CMS_PAGE_ID
FROM TBL_PROJ_CMS_PAGE
WHERE PARENT_ID = @.PROJ_CMS_PAGE_ID
ORDER BY PAGE_ORDER

-- Populate the cursor.
OPEN @.page_list_cursor

WHILE (@.@.FETCH_STATUS = 0)
BEGIN
-- Fetch the details for next activity.
FETCH NEXT FROM @.page_list_cursor
INTO @.CHILD_ID
-- Calls itself, i.e
-- as parent to get all child nodes associated with it.
EXECUTE deleteCMSPage @.CHILD_ID
END
-- Close the cursor.
CLOSE @.page_list_cursor
-- Deallocate the cursor.
DEALLOCATE @.page_list_cursor
DELETE TBL_PROJ_CMS_PAGE
WHERE PROJ_CMS_PAGE_ID = @.PROJ_CMS_PAGE_ID
end
GO

The global variable @.@.FETCH_STATUS is set only when you perform a fetch. So the WHILE loop condition is wrong. You should code it like:

while (1=1)

begin

fetch...

if @.@.fetch_status < 0 break

...

end

|||

I have change the code, and right now it only delete the first branch, eg. root, first child, child of first child ..etc, I am not sure why.

CREATE PROCEDURE [deleteCMSPage]
(@.PROJ_CMS_PAGE_ID [int])

AS

/* do not delete page id 1 */
if @.PROJ_CMS_PAGE_ID <> 1
begin
-- For storing the list of child activities.
DECLARE @.page_list_cursor CURSOR
DECLARE @.CHILD_ID INTEGER
/* get the children of this page, for every child, call deleteCMSPage */
SET @.page_list_cursor = CURSOR FOR
SELECT PROJ_CMS_PAGE_ID
FROM TBL_PROJ_CMS_PAGE
WHERE PARENT_ID = @.PROJ_CMS_PAGE_ID
ORDER BY PAGE_ORDER

-- Populate the cursor.
OPEN @.page_list_cursor

while (1=1)
BEGIN

-- Fetch the details for next activity.
FETCH NEXT FROM @.page_list_cursor
INTO @.CHILD_ID
-- Calls itself, i.e
-- as parent to get all child nodes associated with it.
EXECUTE deleteCMSPage @.CHILD_ID
if @.@.fetch_status < 0 break
END
-- Close the cursor.
CLOSE @.page_list_cursor
-- Deallocate the cursor.
DEALLOCATE @.page_list_cursor
DELETE TBL_PROJ_CMS_PAGE
WHERE PROJ_CMS_PAGE_ID = @.PROJ_CMS_PAGE_ID
end
GO

|||

You have the CHECK for FETCH in the wrong place not like what I showed. Here you are checking FETCH_STATUS of some fetch operation that happened within the delete SP not the one before the call to the SP. So you should change below:

-- Fetch the details for next activity.
FETCH NEXT FROM @.page_list_cursor
INTO @.CHILD_ID

-- Calls itself, i.e
-- as parent to get all child nodes associated with it.
EXECUTE deleteCMSPage @.CHILD_ID
if @.@.fetch_status < 0 break

to:

-- Fetch the details for next activity.
FETCH NEXT FROM @.page_list_cursor
INTO @.CHILD_ID

if @.@.fetch_status < 0 break

-- Calls itself, i.e
-- as parent to get all child nodes associated with it.
EXECUTE deleteCMSPage @.CHILD_ID

|||thanks for you help, it's working now. :)