1

This code should delete an album but I don't know why it still doesn't work

declare variable al_eliminare integer;
begin
    select count(album.id) from album where id = :id_album
    into :al_eliminare;
    if(al_eliminare = 0)
        then delete from album where album.id = id_album;
  suspend;
end
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Octavian Fusari
  • 19
  • 3
  • 2
    `where album.id = :id_album;` maybe? –  Jan 10 '20 at 09:40
  • 3
    Why testing the existance of the row before deletion... if it does not exists, the delete will not remove any row. – Nicola Lepetit Jan 10 '20 at 09:53
  • 1) `select count(album.id) from album` might be very expensive operation on Interbase/Firebird line of servers. It better be avoided. Or else, for niche unusual cases, better be issued very often and server cahce to be significantly increased from default. But rule of thumb is to AVOID `count`ing rows unless you really realyl need the exact count (which still only would be reliable in `Snapshot` grade transactions). – Arioch 'The Jan 10 '20 at 13:28
  • 2) your code makes no sense, it says "if there is no rows - then delete roes that do not exists; if there is some rows then do not delete them". It plainly makes no sense. – Arioch 'The Jan 10 '20 at 13:29
  • 3) using `suspend` there makes no sense too. It is used in `selectable procedures`, those that you `SELECT ... FROM`. But then the code should set velues for the output data columns that are supposed to be `select`ed from the procedure. Your code does not. – Arioch 'The Jan 10 '20 at 13:31
  • 4) frankly, using variable here is redundant. You could put your whole `select` into `if` condition and delete the variable. Yet better would be AVOIDING `count` - `if ( [not] exists( select 1 from album where... ) ) then delete`. And the best option would be just to remove that delete at all, Nicola is spot on there. – Arioch 'The Jan 10 '20 at 13:37
  • MAYBE you actually meant two different tables - but then you even do not need a procedure, just issue something like `delete from albums A where A.id = :Param and not exists ( select 1 from album_songs S where S.album = A.id )` – Arioch 'The Jan 10 '20 at 13:40

1 Answers1

1

When using stored procedure parameters or variables in a statement, you must prefix the parameter or variable name with a colon :.

In the delete statement

delete from album where album.id = id_album

The Firebird parser will consider id_album a column name, and no such column exists in the album table. That is why you get the error.

To indicate it is a stored procedure variable or parameter, prefix with ::

delete from album where album.id = :id_album
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197