Error in case in mysql for creating procedure

Question

create procedure temp (in empId int)
begin
 declare emptype varchar;
    select emptype = qoute(emptype) from dms_document where id =  empid;
    select emptype

 case  
when emptype = 'P' then
    select doctype from dms_report where pilot = 1
else
select 'No Documents required'
end case
end;

This is my query i am creating procedure in MySQL, i am getting error in case statement please hlep me why this error is coming how declare case statement why error is coming in workbench for creating procdure

i am getting Error in Select and case key word when trying to create procdure. — Research Development, Jun 26 '15 at 12:35
Pro SO tip - when you're asking a question, you should always put the actual error messages in the question. Please read this http://stackoverflow.com/help/how-to-ask and improve your question. — Dhanuka, Jun 26 '15 at 12:40

score 1 · Answer 1 · edited Jun 26 '15 at 12:36

1

You are missed comma.

    CREATE PROCEDURE temp (IN empId INT)
        BEGIN
         DECLARE emptype VARCHAR;
            SELECT emptype = qoute(emptype) FROM dms_document WHERE id =  empid;
                SELECT emptype,

     CASE  
    WHEN emptype = 'P' THEN doctype;

    ELSE 'No Documents required';
    END CASE ;
    FROM dms_report WHERE pilot = 1
End;

edited Jun 26 '15 at 12:36

Dhanuka

2,826
5
27
38

answered Jun 26 '15 at 12:07

Venkatesh Panabaka

2,064
4
19
27

now error coming in select keyword SELECT doctype FROM dms_report WHERE pilot = 1 in this line – Research Development Jun 26 '15 at 12:11
any solution why this Error coming in select key word – Research Development Jun 26 '15 at 12:18
It's executing or not? – Venkatesh Panabaka Jun 26 '15 at 12:21
No now again Error on Syntax of ELSE END CASE SYNTAX ERROR i dont know why this Error coming – Research Development Jun 26 '15 at 12:23
NO i am trying to run it in Venkatesh Panabaka mysql workbench i dont know why this error coming – Research Development Jun 26 '15 at 12:24
please tell me how to fix it . – Research Development Jun 26 '15 at 12:29

score 0 · Accepted Answer · edited May 23 '17 at 10:26

Here I formatted your procedure Use delimiter

DELIMITER //
CREATE PROCEDURE temp ( empId INT)
    BEGIN
     DECLARE var_etype VARCHAR(36);

SELECT 
    emptype = QOUTE(emptype)
FROM
    dms_document
WHERE
    id = empid;
SELECT 
    emptype,
    CASE
        WHEN emptype = 'P' THEN doctype
        ELSE 'No Documents required'
    END
FROM
    dms_report
WHERE
    pilot = 1;
End//
DELIMITER ;

Error in case in mysql for creating procedure

2 Answers2