In a web application, I have a tree view which uses a self referencing table (ID, PARENTID)
I use the sproc below to delete pages and also their related child pages. It works as expected.
I'd like to be able to COPY a page as well as all its child pages and insert them into them table.
I guess I'll need some sort of loop but not sure how??
All help appreciated.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[TreeKB_DeleteSiteConByPageID]
WITH OrganisationChart (Id, parentid)
(SELECT Id, parentid
WHERE id = @id -- pass in the variable here
SELECT sc.Id, sc.parentid
FROM TreeKB_tbl_Content sc INNER JOIN OrganisationChart
ON sc.parentid = OrganisationChart.Id)
DELETE FROM TreeKB_tbl_Content
INNER JOIN OrganisationChart
ON TreeKB_tbl_Content.Id = OrganisationChart.Id