0

Hi I'd like to write a stored procedure in mysql that returns different type of tables but every time only one row. I don't have a possibility to use the function that can recrive multiple resultsets. But it would be nice if I could use something like this:

main:BEGIN
    IF EXISTS(SELECT 1 
                FROM Ban
               WHERE (`Ban`.`Name` = _IP OR `Ban`.`IP` = _IP)
                 AND `Ban`.`Date` > _Date LIMIT 1)
    THEN BEGIN
        SELECT 0 AS LoginType, `Ban`.`RegID`, `Ban`.`Name`, `Ban`.`Reason`,
               `Ban`.`Date`, `Eredmeny`.`Name`, `Ban`.`UnbanDate`, `Ban`.`BanType` 
          FROM `Ban` 
          LEFT OUTER JOIN (SELECT DISTINCT RegID, Name 
                             FROM ((SELECT Players.RegID,Players.Name FROM Players)
                                    UNION ALL
                                   (SELECT PlayersArchive.RegID, PlayersArchive.Name
                                     FROM PlayersArchive)) Eredmeny) AS Eredmeny
                       ON Ban.RegID = Eredmeny.RegID
         WHERE (`Ban`.`Name` = _Name OR `Ban`.`IP` = _IP)
           AND `Ban`.`Date` > _Date LIMIT 1;
        LEAVE main;
        END;
    END IF;

    IF EXISTS(SELECT 1 FROM `Players` WHERE `Name` = _Name LIMIT 1)
    THEN BEGIN
        SELECT 1 AS LoginType,Players.*
          FROM `Players` WHERE `Name` = _Name LIMIT 1;
        LEAVE main;
    END;
    END IF;

    IF EXISTS(SELECT 1 FROM `PlayersArchive` WHERE `Name` = _Name LIMIT 1)
    THEN BEGIN
        SELECT 2 AS LoginType,PlayersArchive.*
          FROM `PlayersArchive`
         WHERE `Name` = _Name LIMIT 1;
        LEAVE main;
    END;
    END IF;  

    IF EXISTS(SELECT 1 FROM `PreReg` WHERE `Name` = _Name LIMIT 1)
    THEN BEGIN
        SELECT 3 AS LoginType,`Name`, `Password`, `Email`, `RegDate`, `Level`,
               `StuntDone`, `DriftDone`, `ParkourDone`, `RolePlayDone`,
               `DeathmatchDone`, `PosX`, `PosY`, `PosZ`, `PosA`
          FROM `PreReg`
         WHERE `Name` = _Name LIMIT 1;
        LEAVE main;
    END;
    END IF;

    SELECT 4 AS LoginType LIMIT 1;
END

Another idea is to create a function and in the function create the four tables in the if blocks fill the table and return the table but I don't really know that if it can work or is it possible. But if it is the next problem is that I don't really want to add every new columns if there is a new so it can't be automatic I think.

_Name is a VARCHAR(24)
_IP is VARCHAR(16)
_Date is INT(11)
Yaroslav
  • 6,476
  • 10
  • 48
  • 89
  • off topic, but you should be saving `_Date` as `DATE` instead of `INT(11)` – amaster Sep 02 '13 at 19:41
  • It has it's reason why I save it like that – user2594985 Sep 02 '13 at 19:46
  • Get used to format the code, it will make it more readable and understable for anyone willing to help you. And at the end, it will help you too. Also avoid using `*`, check these [several diffent answers](http://stackoverflow.com/questions/262450/why-is-using-to-build-a-view-bad) on why not do it – Yaroslav Sep 02 '13 at 20:08

0 Answers0