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?

John Woo · Answer 1 · 2013-01-06T09:55:28.330

8

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 ;

edited Jan 06 '13 at 09:55

answered Jan 06 '13 at 08:00

John Woo

258,903
69
498
492

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 – Raed Alsaleh Jan 06 '13 at 08:01
1

see my updated answer, need to add `BEGIN/END` and change the `DELIMITER` – John Woo Jan 06 '13 at 08:03
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" – Raed Alsaleh Jan 06 '13 at 08:07
sorry `&&` has special meaning. change it to `//` instead. – John Woo Jan 06 '13 at 08:11
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 " – Raed Alsaleh Jan 06 '13 at 08:14
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/22254/discussion-between-user1915102-and-jw) – Raed Alsaleh Jan 06 '13 at 08:17
Sorry, my company prevent chat discussion ! – Raed Alsaleh Jan 06 '13 at 08:19
drop the procedure first, then execute the procedure above starting from delimiter – John Woo Jan 06 '13 at 08:20
I found the solution : I need to add additional 'end if', thank you – Raed Alsaleh Jan 06 '13 at 08:33

score 0 · Accepted Answer · answered Jan 06 '13 at 08:36

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;

there should not be space between ELSE IF actually it should be ELSEIF (@FileRef is not null) THEN — Riz, Jun 03 '15 at 13:58

score 0 · Answer 3 · answered Jan 06 '13 at 08:50

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;

MySql stored procedure else if and multi queries

3 Answers3