MySql stored procedure else if and multi queries

Question

In the following stored procedure I encountered an error:

DROP PROCEDURE IF EXISTS `SP_DeleteFileInfo`;
CREATE  PROCEDURE `SP_DeleteFileInfo`(pFileID int)
Set @FileRef= null;
select filesinfo.ReferenceID into @FileRef FROM filesinfo where filesinfo.FileID =pFileID;
DELETE From filesinfo
WHERE filesinfo.FileID= pFileID;
IF EXISTS(SELECT * from filesrefrences where filesrefrences.ReferenceID= @FileRef and filesrefrences.RefrenceCount>1) then 
    update filesrefrences
    set 
        filesrefrences.RefrenceCount= filesrefrences.RefrenceCount-1
    where 
        filesrefrences.ReferenceID= @FileRef;
    SELECT @FileRef;
ELSE IF(@FileRef is Not NULL) THEN
    DELETE from filesrefrences WHERE filesrefrences.ReferenceID = @FileRef;
    SELECT -77;
ELSE
    SELECT -99;
end if;

The error encountered is :

[SQL]
 select filesinfo.ReferenceID into @FileRef FROM filesinfo where filesinfo.FileID =pFileID;
[Err] 1054 - **Unknown column 'pFileID' in 'where clause'**

What would the best solution to solve this error?

Symptomatic

Answer 1

I had to add an additional "End IF" to cover "Else IF" statement the full query be like this :

CREATE  PROCEDURE `SP_DeleteFileInfo`(IN pFileID int)
BEGIN    
Set @FileRef= null;
    select filesinfo.ReferenceID into @FileRef FROM filesinfo where filesinfo.FileID =pFileID;
    DELETE From filesinfo WHERE filesinfo.FileID= pFileID;
    IF EXISTS(SELECT * from filesrefrences where filesrefrences.ReferenceID= @FileRef and filesrefrences.RefrenceCount>1) then 
        update filesrefrences
        set  filesrefrences.RefrenceCount= filesrefrences.RefrenceCount-1
        where  filesrefrences.ReferenceID= @FileRef;
        SELECT @FileRef;
    ELSE IF(@FileRef is Not NULL) THEN
        DELETE from filesrefrences WHERE filesrefrences.ReferenceID = @FileRef;
        SELECT -77;
    ELSE
        SELECT -99;
    end if;
end if;
END;

Symptomatic

there should not be space between ELSE IF actually it should be ELSEIF (@FileRef is not null) THEN – Morin

Answer 2

define pFileID as IN parameter, eg

CREATE PROCEDURE SP_DeleteFileInfo(IN pFileID int)

full query,

DROP PROCEDURE IF EXISTS `SP_DeleteFileInfo`;
DELIMITER //
CREATE  PROCEDURE `SP_DeleteFileInfo`(IN pFileID int)
BEGIN
    Set @FileRef= null;
    select filesinfo.ReferenceID into @FileRef FROM filesinfo where filesinfo.FileID =pFileID;
    DELETE From filesinfo WHERE filesinfo.FileID= pFileID;
    IF EXISTS(SELECT * from filesrefrences where filesrefrences.ReferenceID= @FileRef and filesrefrences.RefrenceCount>1) then 
        update filesrefrences
        set  filesrefrences.RefrenceCount= filesrefrences.RefrenceCount-1
        where  filesrefrences.ReferenceID= @FileRef;
        SELECT @FileRef;
    ELSE 
        IF(@FileRef is Not NULL) THEN
            DELETE from filesrefrences WHERE filesrefrences.ReferenceID = @FileRef;
            SELECT -77;
        ELSE
            SELECT -99;
        END IF;
    END IF;
END //
DELIMITER ;

Trover

I do, but I still face the same problem. do I need to add ** begin/End ** clauses? if yes ? how could I do that to the cover the Queries in the SP – user1915102 25 secs ago edit – Symptomatic

see my updated answer, need to add BEGIN/END and change the DELIMITER – Trover

I see, but Now I face this Error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '&& DELIMITER' at line 17" – Symptomatic

sorry && has special meaning. change it to // instead. – Trover

I gigged you but the error still exist with new symbols : "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '// DELIMITER' at line 17 " – Symptomatic

let us continue this discussion in chat – Symptomatic

Sorry, my company prevent chat discussion ! – Symptomatic

drop the procedure first, then execute the procedure above starting from delimiter – Trover

I found the solution : I need to add additional 'end if', thank you – Symptomatic

Answer 3

You have more than one syntax error

DROP PROCEDURE IF EXISTS `SP_DeleteFileInfo`;
-- MISSING DELIMITER //
CREATE  PROCEDURE `SP_DeleteFileInfo`(pFileID int)
-- MISSING BEGIN
  Set @FileRef= null;
  select filesinfo.ReferenceID into @FileRef FROM filesinfo where filesinfo.FileID =pFileID;
  DELETE From filesinfo
  WHERE filesinfo.FileID= pFileID;
  IF 
    EXISTS(SELECT * from filesrefrences where filesrefrences.ReferenceID= @FileRef and filesrefrences.RefrenceCount>1) 
  then 
    update filesrefrences
    set 
        filesrefrences.RefrenceCount= filesrefrences.RefrenceCount-1
    where 
        filesrefrences.ReferenceID= @FileRef;
    SELECT @FileRef;
  ELSE 
    IF
      (@FileRef is Not NULL) 
    THEN
      DELETE from filesrefrences WHERE filesrefrences.ReferenceID = @FileRef;
      SELECT -77;
    ELSE
      SELECT -99;
    end if;
-- MISSING END IF;
-- MISSING END//
-- MISSING DELIMITER ;

And you get this error

[SQL] select filesinfo.ReferenceID into @FileRef FROM filesinfo where filesinfo.FileID =pFileID; [Err] 1054 - Unknown column 'pFileID' in 'where clause'

because the Stored Procedure you create is this

CREATE  PROCEDURE `SP_DeleteFileInfo`(pFileID int)
Set @FileRef= null;

All other statements are normal single statements and therefor pFileID is unknown to this context.

Just try this select statement standalone yourself and you will get the same error.

select filesinfo.ReferenceID into @FileRef FROM filesinfo where filesinfo.FileID =pFileID;

Royal