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)