0

I'm attempting to write a MySQL Stored Procedure but I can't seem to get it to compile with multiple OUT parameters after selecting from the table. And I couldn't find any specific examples for what I'm attempting to do. The compiler error states, "UNEXPECTED INTO, expecting ; " And also, it says the FirstName column is not declared. Any help would be appreciated.

 CREATE PROCEDURE `SP_SelectAllFighters`(out o_FighterID int, out o_FirstName varchar(45), out o_MiddleName varchar(45), out o_LastName varchar(45), o_FullName varchar(155), out o_WeightClass varchar(45), out o_TeamAssociation varchar(45), out o_Organization varchar(45))
BEGIN
    SELECT
        ID into o_FighterID,
        -- CONCAT_WS(' ', FirstName, MiddleName, LastName) into o_FullName,
        FirstName into o_FirstName,
        MiddleName into o_MiddleName,
        LastName into o_LastName,
        WeightClass into o_WeightClass,
        TeamAssociation into o_TeamAssociation,
        Organization into o_Organization
    FROM
        COMPETITOR;
END
Haim Evgi
  • 123,187
  • 45
  • 217
  • 223
Rion
  • 545
  • 1
  • 3
  • 10

1 Answers1

0

I thought you could do it like this:

SELECT o_FighterID = ID,
    o_FullName = CONCAT_WS(' ', FirstName, MiddleName, LastName),
    o_FirstName = FirstName,
    etc.
FROM
    COMPETITOR;

but after reviewing the MySQL reference, it looks like Select into syntax does it like this:

SELECT ID, FirstName, ... into o_FighterID, o_FirstName, ...
FROM
    COMPETITOR;

Also, you may want to think about using table aliases if it's claiming your column names aren't declared.

http://dev.mysql.com/doc/refman/5.0/en/select-into.html

user3863111
  • 38
  • 3