Problem with a stored procedure

[Sykofanten]

create procedure deleteGamlekund er

@aar datetime
AS
DECLARE @Get_kunde_Id bigint

DECLARE Get_kunde_Id_it erator CURSOR FOR
select kunde.kunde_id
from kunde
where kunde_oprettels es_dato < @aar
and kunde.kunde_id = (select privatkunde.pri vatkunde_id from privatkunde where privatkunde.pri vatkunde_id = kunde.kunde_id)
and kunde.kunde_id = (select foreningskunde_ id from foreningskunde where foreningskunde. foreningskunde_ id = kunde.kunde_id)

begin /* procedure */

OPEN Get_kunde_Id_it erator
FETCH NEXT FROM Get_kunde_Id_it erator INTO @Get_kunde_Id
print '@@FETCH_STATUS :'
print @@FETCH_STATUS

WHILE (@@FETCH_STATUS = 0) /* @@FETCH_STATUS : 0 hvis fetch lykkedes, -1 hvis der er fejl, -2 hvis der ikke returneres flere */
BEGIN /* WHILE*/
print 'ID :'
print @Get_kunde_Id
Begin transaction
DELETE FROM privatkunde WHERE privatkunde.pri vatkunde_Id = @Get_kunde_Id
DELETE FROM foreningskunde WHERE foreningskunde. foreningskunde_ id = @get_kunde_id
DELETE FROM kunde WHERE kunde.kunde_Id = @Get_kunde_Id
commit transaction
FETCH NEXT FROM Get_kunde_Id_it erator INTO @Get_kunde_Id
END /* WHILE*/

CLOSE Get_kunde_Id_it erator
deallocate Get_kunde_Id_it erator /* cursor fjernes fra RAM */

end /* procedure */

execute deletegamlekund er 2008

You can se the database here: http://img46.imageshac k.us/img46/5699/dbbj1.jpg

The problem is, that it doesnt delete from those tables... I just don't know why :/

[scripto]

doing a quick look -
your stored proc expects a parameter of datetime
execute deletegamlekund er '2008-01-01 00:00:000'
it needs a real date time, not just the year.

it's also possible that your select statment in the cursor may be returning multiple values from the foreign key table - that is not good.

[iburyak]

Try this way. Never user cursor unless it is necessary.

[PHP]BEGIN TRAN

Delete from kunde
from kunde a
join privatkunde b on a.kunde_id = b.privatkunde_i d
join foreningskunde c on a.kunde_id = foreningskunde_ id
where kunde_oprettels es_dato < @aar


Delete from privatkunde
from kunde a
join privatkunde b on a.kunde_id = b.privatkunde_i d
join foreningskunde c on a.kunde_id = foreningskunde_ id
where kunde_oprettels es_dato < @aar


Delete from foreningskunde
from kunde a
join privatkunde b on a.kunde_id = b.privatkunde_i d
join foreningskunde c on a.kunde_id = foreningskunde_ id
where kunde_oprettels es_dato < @aar

COMMIT TRAN[/PHP]