Unknown Column 'template' in where clause, while using a stored procedure.

Question

Hello, 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

Answer 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.