MySql Syntax

Question

I am Sql Server 2008 guy I am using MySql for the first the time the syntax is kicking my butt for the stored procedure. Can you tell me what i am doing wrong below. I am getting the error in the where clause. -- -------------------------------------------------------------------------------- -- Routine DDL -- Note: comments before and after the routine body will not be stored by the server -- -------------------------------------------------------------------------------- DELIMITER $$ CREATE PROCEDURE `sm3iiasensorseekerdatabase`.`prc_GetTestInfoBycolorSerialNumber` ( IN colorSerialNumber VARCHAR (50) ) BEGIN SELECT table1.colorSerialNumber table1.colorPartNumber table1.comments table2.ID table2.colorName table2.colorCreationStartTime  table2.colorOperator FROM table1 INNER JOIN table2 ON seeker.colorPartNumber = table2.color_PartNumber AND table1.colorSerialNumber = table2.color_SerialNumber WHERE (table1.SerialNumber Like '' + @colorSerialNumber + '%'); END

David Smith · Answer

But can you tell me whats wrong with my where close below. I really want to know. In workbench i get the highlighted red error on the + sign WHERE (table1.SerialNumber Like '' + @colorSerialNumber + '%');

Deepak Sharma · Answer

Hi You can use EXECUTE statement to correct this. Use following statement between BEGIN and END: SET @param1=CONCAT(colorSerialNumber '%'); SET @sql= 'SELECT table1.colorSerialNumber table1.colorPartNumber table1.comments table2.ID table2.colorName table2.colorCreationStartTime  table2.colorOperator FROM table1 INNER JOIN table2 ON seeker.colorPartNumber = table2.color_PartNumber AND table1.colorSerialNumber = table2.color_SerialNumber WHERE (table1.SerialNumber Like ?)'; PREPARE statement1 FROM @sql; EXECUTE statement1 USING @param1; DEALLOCATE PREPARE statement1;