1

I have a stored procedure, which has got executed without any errors, but gives me an error "#1054: Unknown column 'templateName' in where clause" when I run it.

The stored procedure is:

delimiter //
DROP PROCEDURE `getData`//
CREATE DEFINER=`root`@`localhost` PROCEDURE `getData`(IN templateName VARCHAR(45),IN templateVersion VARCHAR(45),IN userId VARCHAR(45))
BEGIN
    set @version = CONCAT("SELECT 'saveOEMsData_answersVersion' FROM `saveOEMsData` where 'saveOEMsData_templateName' = ",templateName," and 'saveOEMsData_templateVersion' = ",templateVersion," and 'saveOEMsData_userId'= ",userId); 
    PREPARE s1 from @version;
    EXECUTE S1;
END
//
delimiter ;

Now I call it using call getData('templateName','1','285');. And whenever I call it, I get the mentioned error. What could the problem be?? It surely is syntactical, I have been reading the mysql manuals for 2 days and have come out without!!

Any help would be great!! Thanks

Jack M.
  • 30,350
  • 7
  • 55
  • 67
sai
  • 4,907
  • 4
  • 24
  • 18

1 Answers1

1

You pass the 'templateName' as string to the procedure and the part of SQL is then

saveOEMsData_templateVersion = templateName

if that is supposed to be a string value then quote it (only relevant part)

...'saveOEMsData_templateVersion' = '",templateVersion,"'...

EDIT: As I said, put the temmplateName (the parameter) in quotes ('), and for the field names in the composed SELECT keep them in slant quotes like `

delimiter //
DROP PROCEDURE `getData`//
CREATE DEFINER=`root`@`localhost` PROCEDURE `getData`(IN templateName VARCHAR(45),IN templateVersion VARCHAR(45),IN userId VARCHAR(45))
BEGIN
    set @version = CONCAT("SELECT `saveOEMsData_answersVersion` FROM `saveOEMsData` WHERE `saveOEMsData_templateName` = '",templateName,"' AND `saveOEMsData_templateVersion` = ",templateVersion," AND `saveOEMsData_userId`= ",userId); 
    PREPARE s1 from @version;
    EXECUTE S1;
END
//
delimiter ;

Note: hope you are sanitizing your input.

Community
  • 1
  • 1
Unreason
  • 12,556
  • 2
  • 34
  • 50
  • ok I have to use the value of "saveOEMsData_answersVersion" which is being retreived using the select statement. I have to use it for an IF LOOP, how is that possible?? set @version = CONCAT("SELECT 'saveOEMsData_answersVersion' FROM `saveOEMsData` where saveOEMsData_templateName = '",templateName,"' and saveOEMsData_templateVersion = ",templateVersion," and saveOEMsData_userId= ",userId," GROUP BY `saveOEMsData_templateName`"); PREPARE S1 from @version; EXECUTE S1; IF(S1 == 1) THEN select 'one'; ELSE select 'zero'; END IF; I am getting an error when I do this?? – sai Apr 19 '10 at 21:46
  • you are mixing your quotes; I updated the answer, cleaning what I could see. try to understand the syntax of the procedure and work through easier examples. – Unreason Apr 19 '10 at 23:00