Stored procedure question

MySql

Stored procedure question

---

crosscreek 03-22-2008, 8:44 AM
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??

Re: Stored procedure question

---

glaivier 07-11-2008, 12:23 PM
As I recall, the optional bounding markers to go around the stored procedure name are right single quote's rather than straight apostrophes.  But without adding those you still need to be switching delimiters when creating a stored procedure (this foxed me at first as a Microsoft guy).

See this mysql dev page:
http://dev.mysql.com/tech-resources/articles/mysql-storedproc.html

If you look down the page you'll see they switch the delimiter (in this case to // with the command "DELIMITER //") and then follow the procedure with this delimiter and then switch it back (so they follow it with "//" and then use "DELIMITER ;" to switch it back to normal).

Make those changes and it should work.