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
Loading
David SmithPosted Feb 11, 2012, 12:52 AM
WHERE (table1.SerialNumber Like '' + @colorSerialNumber + '%');
Deepak SharmaPosted Feb 9, 2012, 1:35 AM
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;