ERROR Creating Stored Procedure MySQL

Question

I want to make a stored procedure in MySQL database using phpmyadmin.

After several minutes I type in "create procedure" dialog box and press GO, i got an error in my syntax.

I've search on google about which one is wrong, but still not found. Please anyone give me some advice how to fix this problem.

This is my 'MySQL' stored procedure query.

DELIMITER $$
BEGIN
IF cek=1 THEN
    SELECT a.NIK AS NIK
            , a.EmployeeName AS EmployeeName
            , a.Position AS Position
            , b.TypeName AS EmployeeType
            , TIME(c.EventTime) AS Datang
            , (CASE WHEN a.TypeID=3 THEN 
                    (CASE WHEN TIME(c.EventTime)>(SELECT TimeIn FROM MST_Shift 
                    WHERE ShiftID=(SELECT ShiftID FROM SCH_Shifting WHERE NIK=a.NIK AND DATE(ShiftDate)=DATE(sdate)
                    AND IsActive=1 AND IsDeleted=0))
                    THEN 'Late' ELSE 'On Time' END)
              WHEN a.TypeID=1 OR a.TypeID=2 THEN
                    (CASE WHEN TIME(c.EventTime)>'08:15:00' THEN 'Late' ELSE 'On Time' END)
              WHEN a.TypeID=6 OR a.TypeID=7 THEN
                    (CASE WHEN TIME(c.EventTime)>(SELECT TimeIn FROM MST_Shift
                    WHERE TypeID=a.TypeID LIMIT 1) THEN 'Late' ELSE 'On Time' END)
              WHEN a.TypeID=5 THEN
                    (CASE WHEN TIME(c.EventTime)>(SELECT TimeIn FROM MST_Shift
                    WHERE ShiftID=(CASE WHEN DAYOFWEEK(sdate)=7 THEN 12 ELSE 11 END)) THEN 'Late' ELSE 'OnTime' END)
              WHEN a.TypeID=4 THEN
                    (CASE WHEN TIME(c.EventTime)>(SELECT TimeIn FROM SCH_Special
                    WHERE NIK=a.NIK AND DATE(AbsenceDate)=DATE(sdate) AND IsDeleted=0) THEN 'Late' ELSE 'OnTime' END)
              ELSE 'Undefined' END) AS Description
    FROM MST_Employee a
    INNER JOIN MST_EmployeeType b ON b.TypeID=a.TypeID
    INNER JOIN VW_AttendanceIN c ON a.NIK=c.NIK
    WHERE DATE(c.EventTime)=DATE(sdate)
    GROUP BY b.TypeName
            , a.Position
            , a.EmployeeName;
ELSE
    SELECT a.NIK AS NIK
            , a.EmployeeName AS EmployeeName
            , a.Position AS Posisi
            , b.TypeName AS Tipe
            , TIME(c.EventTime) AS Datang
            , (CASE WHEN a.TypeID=3 THEN 
                    (CASE WHEN TIME(c.EventTime)>(SELECT TimeIn FROM MST_Shift 
                    WHERE ShiftID=(SELECT ShiftID FROM SCH_Shifting WHERE NIK=a.NIK AND DATE(ShiftDate)=DATE(sdate)
                    AND IsActive=1 AND IsDeleted=0))
                    THEN 'Late' ELSE 'On Time' END)
              WHEN a.TypeID=1 OR a.TypeID=2 THEN
                    (CASE WHEN TIME(c.EventTime)>'08:15:00' THEN 'Late' ELSE 'On Time' END)
              WHEN a.TypeID=6 OR a.TypeID=7 THEN
                    (CASE WHEN TIME(c.EventTime)>(SELECT TimeIn FROM MST_Shift
                    WHERE TypeID=a.TypeID LIMIT 1) THEN 'Late' ELSE 'On Time' END)
              WHEN a.TypeID=5 THEN
                    (CASE WHEN TIME(c.EventTime)>(SELECT TimeIn FROM MST_Shift
                    WHERE ShiftID=(CASE WHEN DAYOFWEEK(sdate)=7 THEN 12 ELSE 11 END)) THEN 'Late' ELSE 'OnTime' END)
              WHEN a.TypeID=4 THEN
                    (CASE WHEN TIME(c.EventTime)>(SELECT TimeIn FROM SCH_Special
                    WHERE NIK=a.NIK AND DATE(AbsenceDate)=DATE(sdate) AND IsDeleted=0) THEN 'Late' ELSE 'OnTime' END)
              ELSE 'Undefined' END) AS Description
    FROM MST_Employee a
    INNER JOIN MST_EmployeeType b ON b.TypeID=a.TypeID
    INNER JOIN VW_AttendanceIN c ON a.NIK=c.NIK
    WHERE DATE(c.EventTime)=DATE(sdate)
    AND b.TypeID=type
    AND (a.NIK LIKE '%'+search+'%' OR a.EmployeeName LIKE '%'+key+'%')
    GROUP BY b.TypeName
            , a.Position
            , a.EmployeeName;
END IF;
END$$

I got error like this

processing your request:

The following query has failed: "CREATE DEFINER=root@% PROCEDURE USP_SelectAttendance(IN cek BIT(1), IN type INT, IN sdate DATETIME, IN search VARCHAR(100)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER .......

MySQL said: #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 '$$ BEGIN IF cek=1 THEN SELECT a.NIK AS NIK , a.EmployeeName AS ' at line 1

In addition, the 'SELECT' query is work fine when i run it in SQL.

The error was appear in stored procedure creation.

PS: I'm sorry for bad english

wchiquito wchiquito · Accepted Answer · 2014-03-28T15:54:47

Change:

...
AND (a.NIK LIKE '%'+search+'%' OR a.EmployeeName LIKE '%'+key+'%')
...

by:

...
AND (a.NIK LIKE CONCAT('%',search,'%') OR a.EmployeeName LIKE CONCAT('%',`key`,'%'))
...

SQL Fiddle demo

ERROR Creating Stored Procedure MySQL

1 Answers