 03-22-2008, 8:44 AM
|
crosscreek
Joined on 03-23-2008
Posts 1
|
Stored procedure question
|
|
|
|
|
Trying to create a stored procedure but keep getting a syntax error....I used the example in the post here, but didn't work.
CREATE PROCEDURE sp_pedigreestored(myInput int)
BEGIN
SELECT tblDOG.ID AS ID, tblDOG.NAME AS NAME, tblDOG.PreTi, tblDOG.SuffTi, tblDOG.callname, tblDOG.DOB AS Birth, tblGEN.Gender as Gender, tblColor.Color, tblDOG.Owner, tblDOG.Breeder, tblDOG.DOD AS Death, tblDOG.website, tblDOG.Hips, tblDOG.Eyes, tblDOG.elbow, tblDOG.CNM, tblDOG.UKC, tblDOG.AKC, dam.ID AS damid, sire.ID AS sireid, Ssire.id AS ssireid, Sdam.ID AS sdamid, Dsire.ID as dsireid, Ddam.ID AS Ddamid, SSsire.ID AS sssireid, SSdam.ID AS ssdamid, SDsire.ID AS sdsireid, SDdam.ID AS sddamid, DSsire.ID AS dssireid, DSdam.ID AS dsdamid, DDsire.ID AS ddsireid, DDdam.ID as dddamid, CONCAT_WS(' ', tbldog.PreTi, tbldog.NAME, tbldog.suffti) AS Fullname, CONCAT_WS(' ', sire.PreTi, sire.NAME, sire.suffti, '(',scolor.color,')') AS sirefull, CONCAT_WS(' ', dam.PreTi, dam.NAME, dam.suffti, '(',dcolor.color,')') AS damfull, CONCAT_WS(' ', ssire.PreTi, ssire.NAME, ssire.suffti, '(',SScolor.color,')') AS Ssfull, CONCAT_WS(' ', Sdam.PreTi, Sdam.NAME, Sdam.suffti, '(',SDcolor.color,')') AS Sdfull, CONCAT_WS(' ', Dsire.PreTi, Dsire.NAME, Dsire.suffti, '(',DScolor.color,')') AS DSfull, CONCAT_WS(' ', Ddam.PreTi, Ddam.NAME, Ddam.suffti, '(',DDcolor.color,')') AS DDfull, CONCAT_WS(' ', SSsire.PreTi, SSsire.NAME, SSsire.suffti, '(',SSScolor.color,')') AS SSSfull, CONCAT_WS(' ', SSdam.PreTi, SSdam.NAME, SSdam.suffti, '(',SSDcolor.color,')') AS SSDfull, CONCAT_WS(' ', SDsire.PreTi, SDsire.NAME, SDsire.suffti, '(',SDScolor.color,')') AS SDSfull, CONCAT_WS(' ', SDdam.PreTi, SDdam.NAME, SDdam.suffti, '(',SDDcolor.color,')') AS SDDfull, CONCAT_WS(' ', DSsire.PreTi, DSsire.NAME, DSsire.suffti, '(',DSScolor.color,')') AS DSSfull, CONCAT_WS(' ', Dsdam.PreTi, Dsdam.NAME, Dsdam.suffti, '(',DsDcolor.color,')') AS DsDfull, CONCAT_WS(' ', Ddsire.PreTi, Ddsire.NAME, Ddsire.suffti, '(',Ddscolor.color,')') AS Ddsfull, CONCAT_WS(' ', DDdam.PreTi, DDdam.NAME, DDdam.suffti, '(',DDDcolor.color,')') AS DDDfull FROM ((((((((((((((((((((((((((((tblGEN LEFT JOIN (tblColor LEFT JOIN tblDOG ON tblColor.ColorID = tblDOG.ColorID) ON tblGEN.GenID = tblDOG.genID) LEFT JOIN tblDOG AS sire ON tblDOG.sireID = sire.ID) LEFT JOIN tblDOG AS dam ON tblDOG.damID = dam.ID) LEFT JOIN tblDOG AS Ssire ON sire.sireID = Ssire.ID) LEFT JOIN tblDOG AS Sdam ON sire.damID = Sdam.ID) LEFT JOIN tblDOG AS Dsire ON dam.sireID = Dsire.ID) LEFT JOIN tblDOG AS Ddam ON dam.damID = Ddam.ID) LEFT JOIN tblDOG AS SSsire ON Ssire.sireID = SSsire.ID) LEFT JOIN tblDOG AS SSdam ON Ssire.damID = SSdam.ID) LEFT JOIN tblDOG AS SDsire ON Sdam.sireID = SDsire.ID) LEFT JOIN tblDOG AS SDdam ON Sdam.damID = SDdam.ID) LEFT JOIN tblDOG AS DSsire ON Dsire.sireID = DSsire.ID) LEFT JOIN tblDOG AS DSdam ON Dsire.damID = DSdam.ID) LEFT JOIN tblDOG AS DDsire ON Ddam.sireID = DDsire.ID) LEFT JOIN tblDOG AS DDdam ON Ddam.damID = DDdam.ID) LEFT JOIN tblColor AS Scolor ON sire.ColorID = Scolor.ColorID) LEFT JOIN tblColor AS Dcolor ON dam.ColorID = Dcolor.ColorID) LEFT JOIN tblColor AS SScolor ON Ssire.ColorID = SScolor.ColorID) LEFT JOIN tblColor AS SDcolor ON Sdam.ColorID = SDcolor.ColorID) LEFT JOIN tblColor AS DScolor ON Dsire.ColorID = DScolor.ColorID) LEFT JOIN tblColor AS DDcolor ON Ddam.ColorID = DDcolor.ColorID) LEFT JOIN tblColor AS SSScolor ON SSsire.ColorID = SSScolor.ColorID) LEFT JOIN tblColor AS SSDcolor ON SSdam.ColorID = SSDcolor.ColorID) LEFT JOIN tblColor AS SDScolor ON SDsire.ColorID = SDScolor.ColorID) LEFT JOIN tblColor AS SDDcolor ON SDdam.ColorID = SDDcolor.ColorID) LEFT JOIN tblColor AS DSScolor ON DSsire.ColorID = DSScolor.ColorID) LEFT JOIN tblColor AS DSDcolor ON DSdam.ColorID = DSDcolor.ColorID) LEFT JOIN tblColor AS DDScolor ON DDsire.ColorID = DDScolor.ColorID) LEFT JOIN tblColor AS DDDcolor ON DDdam.ColorID = DDDcolor.ColorID WHERE tblDOG.ID=myInput END;
I get the following error
#1064 - 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 'SELECT tblDOG.ID AS ID, tblDOG.NAME AS NAME, tblDOG.PreTi, tblDO
If i put use 'sp_pedigreestored'(myInput int) then my error is similar, just the sql doesn't like ' ' around the Stored procedure name.
any ideas??
|
|
|
|
|
Report
|
|
|
|