SP: Radera hierarkisk struktur

Hej!

Jag vill skriva en SP där jag anger ett ID, och då raderas alla "barnen".

Min databasstruktur ser ut såhär:

fldCatID fldCatParentID
1 0
2 1
3 1
4 2
5 4
6 4
7 2
8 5

Såhär ser den hierarkiska strukturen ut då:
1
- 2
- 3
- - 4
- - - 5
- - - - 8
- - - 6
- - 7

Om jag tar bort ID 4 skall alltså ID 4,5,6,8 raderas.

create procedure brimbaDelete (@daddyFatSo int)
as
begin
  declare @littleTinyKid int
  declare a cursor for select fldCatID 
   from brimbaTable
   where fldCatParentID = @daddyFatSo

   open a
   fetch a into @littleTinykid
   while @@fetchstatus = 0
   begin
        execute brimbaDelete @littleTinykid
        delete from brimbaTable where fldCatParentID = @daddyFatSo
        fetch a into @littleTinykid
   end 
   close a
   DEALLOCATE a

end

Vad innebär @@fetchstatus ?

Jag ändrade till @@fetch_status

men jag får felmeddelande i query analyzer...:

Server: Msg 16915, Level 16, State 1, Procedure proc_deleteDirectory, Line 9
A cursor with the name 'cursor_a' already exists.
Server: Msg 16905, Level 16, State 1, Procedure proc_deleteDirectory, Line 13
The cursor is already open.
Server: Msg 16915, Level 16, State 1, Procedure proc_deleteDirectory, Line 9
A cursor with the name 'cursor_a' already exists.
Server: Msg 16905, Level 16, State 1, Procedure proc_deleteDirectory, Line 13
The cursor is already open.

(0 row(s) affected)

Server: Msg 16916, Level 16, State 1, Procedure proc_deleteDirectory, Line 19
A cursor with the name 'cursor_a' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure proc_deleteDirectory, Line 21
A cursor with the name 'cursor_a' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure proc_deleteDirectory, Line 22
A cursor with the name 'cursor_a' does not exist.

(2 row(s) affected)

Server: Msg 16916, Level 16, State 1, Procedure proc_deleteDirectory, Line 19
A cursor with the name 'cursor_a' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure proc_deleteDirectory, Line 21
A cursor with the name 'cursor_a' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure proc_deleteDirectory, Line 22
A cursor with the name 'cursor_a' does not exist.

Den raderar alla där fldCatParentID är lika med den ingående parametern.

Jag löste det hela genom att deklarera cursorn lokalt.

declare a cursor local for select fldCatID

Hur gör man detta om man har mySql (inte kan köra SP)?

Du kan göra det kodmässigt och bygga upp en sqlsats som sedan raderar alla de poster som skall raderas, alternativt radera med det samma.

Det blir en del databasanrop iofs, men det går!

Fick en idé:
Radera alla poster ur t1 som vars parentId är 'foo'
Radera alla poster ur t2 vars id är null i t1

Jag tänkte mig något sånt här.. Det finns säkert massor med fel, men ville bara visa hur jag har tänkt..
Du kan ju även bygga en raderingsklass som du matar in i istället för en sträng och så. men men...

    Dim delStr As String
    delStr = ""
    Public Sub getIDsForDelete(ByVal id)
        Dim SQLStmt
        Dim rs

        SQLStmt = "SELECT id, parentID FROM tblLevel WHERE parentID = " & id
        rs = conn.execute(SQLStmt)

        Do Until rs.eof
            delStr = delStr & rs("id") & ","
            deleteH(rs("parentID"))
            rs.movenext()
            If rs.eof Then Exit Do
        Loop

        rs.close()
        rs = Nothing
    End Sub
    getIDsForDelete(4)
    SQLStmt = "delete from tblLevel where id in (" & left(delStr,len(delStr)-1) & ") or id=4"
    conn.execute(SQLStmt)

Ser lovande ut.
Vad är: deleteH(rs("parentID"))?

Provider error 80040e69 skrev:

Session cannot be created because maximum number of active sessions was already reached. Consumer must release one or more sessions before creating a new session object.

call getIDsForDelete(id)

    Response.write ("DELETE FROM site_pages WHERE sp_pageid IN ("& left(delStr,len(delStr)-1) &") OR sp_pageid="& Id)
	
	
	Dim delStr : delStr = ""
    Sub getIDsForDelete(ByVal id)
        Dim SQLStmt, rs
        SQLStmt = "SELECT sp_pageid, sp_parent_pageid FROM site_pages WHERE sp_parent_pageid = " & id
        set rs = Connection.execute(SQLStmt)

        Do Until rs.eof
            delStr = delStr & rs(0) & "," 
			getIDsForDelete(rs(1))   
            rs.movenext()
            If rs.eof Then Exit Do
        Loop
		
        rs.close()
        set rs = Nothing		
		
    End Sub

Fick aldrig till din variant, men hittade ngt liknande på nätet.
Mastodontfet blev den iaf.

     sub SubDEL()
	Dim ReturnList
	Call DeleteC(Id)
	DoIt(Id)
     end sub
	

	Sub DeleteC(ID)
	 Connection.execute("DELETE FROM site_pages WHERE sp_pageid ="& id),,128

	End Sub
	
	
    Function ReturnList(ID)
		On Error Resume Next 
			Dim SQLStmt,rs,ReturnList_Return
			SQLStmt = "SELECT sp_pageid, sp_parent_pageid FROM site_pages WHERE sp_parent_pageid = " & ID
			set rs = Connection.execute(SQLStmt)
			if rs.eof Then ReturnList = 0
			ReturnList_Return = ""
			
			Do Until rs.eof
				ReturnList_Return = ReturnList_Return & rs(0) & "|"
				rs.movenext()
				If rs.eof Then Exit Do
			Loop
			
			ReturnList = Left(ReturnList_Return, Len(ReturnList_Return) - 1)
			
			rs.close() : set rs = Nothing
    End function
	
	
	Sub DoIt(ID)
	Dim parseloop,tempHitList,workingwith,parseloop2,tmpHL
		tempHitList = ReturnList(ID)
		For parseloop = 0 to UBound(Split(tempHitList, "|")) 
			workingwith = Split(tempHitList, "|")(parseloop)
			if workingwith <> "0" and workingwith <> "" then 
				For parseloop2 = 0 to UBound(Split(workingwith, "|")) 
					tmpHL = Split(workingwith, "|")(parseloop2) 
					DeleteC(tmpHL)
					Call DoIt(tmpHL)
				Next
			end if
		Next
	End Sub