CREATE PROCEDURE up_SearchFiles(searchTerm VarChar(100))
BEGIN
declare aye int default 0;
CREATE TEMPORARY TABLE resultstmp (Name VARCHAR(100), PackageID int, FileName VarChar(1000),Arch Varchar(10),RepoURL Varchar(1000),RepoID int, Priority int, Checksum VARCHAR(1000), Version Varchar(50)) ENGINE=MEMORY;
CREATE TEMPORARY TABLE resultstmp2 (Name VARCHAR(100), PackageID int, FileName VarChar(1000),Arch Varchar(10),RepoURL Varchar(1000),RepoID int, Priority int, Checksum VARCHAR(1000), Version Varchar(50)) ENGINE=MEMORY;
loop1: LOOP
set aye := aye +1;
INSERT INTO resultstmp2 SELECT DISTINCT openSUSE_102_Packages.Name AS Name,openSUSE_102_Packages.PackageID AS PackageID,openSUSE_102_Files.FileName AS FileName, openSUSE_102_Packages.Arch AS Arch, openSUSE_102_Repos.RepoURL AS RepoURL, openSUSE_102_Repos.RepoID AS RepoID, openSUSE_102_Repos.Priority AS Priority, openSUSE_102_Packages.Checksum AS Checksum, openSUSE_102_Packages.Version AS Version FROM openSUSE_102_Repos, openSUSE_102_Packages, openSUSE_102_Files WHERE openSUSE_102_Repos.RepoID = openSUSE_102_Packages.RepoID AND openSUSE_102_Packages.PackageID = openSUSE_102_Files.PackageID AND openSUSE_102_Packages.Arch != 'src' AND MATCH (openSUSE_102_Files.FileName) AGAINST (searchTerm IN BOOLEAN MODE) AND openSUSE_102_Packages.PackageID NOT IN (SELECT PackageID FROM resultstmp) LIMIT 1;
INSERT INTO resultstmp (SELECT * FROM resultstmp2);
DELETE FROM resultstmp2;
IF aye < 40 then
iterate loop1;
END IF;
leave loop1;
end loop loop1;
SELECT * FROM resultstmp
ORDER BY
Priority,
Arch,
RepoID;
drop table resultstmp;
drop table resultstmp2;
END|