deleteing all hierarchical records under selected one

You should have the DELETE CASCADE option on, for example:

ALTER TABLE catalog DROP CONSTRAINT aa

ALTER TABLE catalog ADD CONSTRAINT
   (FOREIGN KEY (stock_num, manu_code) REFERENCES stock
   ON DELETE CASCADE CONSTRAINT ab)

And then you just have to delete the parent records, and all the child records will be deleted.

You can do the folowing :

You can create the Procedure in Attach :
It deletes the Node you want and then deletes all the Nodes that do noet have a Existing ParentID

Procedure : (change #tmp towards your tablename ;-) )
 

create procedure DeleteNodeAndChilds
@NodeId int
as 
begin
	delete from #tmp where id =@NodeId
	while ((select count(*) from #tmp where parentid not in (select id from #tmp))>0)
	begin
		delete from #tmp where parentid not in (select id from #tmp)
	end
end

Select allOpen in new window

Example of use
exec DeleteNodeAndChilds 80

Full example
 

create procedure DeleteNodeAndChilds
@NodeId int
as 
begin
	delete from #tmp where id =@NodeId
	while ((select count(*) from #tmp where parentid not in (select id from #tmp))>0)
	begin
		delete from #tmp where parentid not in (select id from #tmp)
	end
end 
Go

create table #TMP
(
id int,
parentid int,
menulevel int
)

insert #tmp select 57, NULL, 1
insert #tmp select 74, 57, 2
insert #tmp select 75, 57, 2
insert #tmp select 76, 57, 2
insert #tmp select 77, 74, 3
insert #tmp select 78, 77, 4
insert #tmp select 79, 75, 2
insert #tmp select 80, 75, 2
insert #tmp select 81, 80, 3
insert #tmp select 82, 80, 3
insert #tmp select 83, 81, 4
insert #tmp select 84, 81, 4

select * from #tmp
exec DeleteNodeAndChilds 80
select * from #tmp
drop procedure DeleteNodeAndChilds
drop table #tmp

Select allOpen in new window

If you are on SQL Server version 2005 or above, you could use a recursive cte -

(Note: Delete statement is commented out so that you can initially verify the selected results and then delete if ok)

declare @idchaintodelete int

set @idchaintodelete = 80  -- Populate the required hierarchy id here

;with cte (id, parentid, menulevel)
as
(	select id, parentid, menulevel
	from mytable
	where id = @idchaintodelete

	union all

	select m.id, m.parentid, m.menulevel
	from mytable m
	inner join cte on cte.id = m.parentid
)
select *
--delete
from cte

Select allOpen in new window

Hi,

Loop this from Bigger Id and delete one by one using cursor or logic like same

Thanks for all the suggestions. I've been trying reb73 suggestion with the modification added by poor_beggar and it all works except I get an error on the #tmp
"Invalid Object name '#tmp'"

Here's my Stored Procedure:
@id as integer

AS

with cte (id, parentid, menulevel)
as
(      select id, parentid, menulevel
      from radmenu
      where id = @id

      union all

      select m.id, m.parentid, m.menulevel
      from radmenu m
      inner join cte on cte.id = m.parentid
)


delete from #tmp where id in (select id from cte )

Thats fixed it thanks