2

I'm currently in the process of writing a stored procedure that uses case statements to determine what queries to run. Inside one of my queries is a case expression and I get a syntax error every time I try to save the altered procedure. The procedure will save fine without that case expression, but fails once it is added. I know the syntax of the expression itself is accurate, so I'm wondering if case expressions are even allowed inside of a case statement.

Example:

CASE
    WHEN x = 1 THEN
        SELECT
            5 AS 'Col1',
            CASE
                WHEN y = 5 THEN 2 
                ELSE 0
            END AS 'Col2'
        FROM table
        WHERE 1=1
        ;
    WHEN x = 2 THEN
        SELECT
            *
        FROM table2
        ;
    ELSE
        SELECT
            *
        FROM table3
        ;
END CASE;

Any help would be greatly appreciated. Is it possible to have a case expression inside a case statement? If so, is it formatted differently? How can I get this to work...?

Thank you!

WoofDg79
  • 131
  • 2
  • 7
  • you can nest case statements as much as you want, but seriously consider AGAINST it, because that's pretty much the definition of spaghetti logic. – Marc B Aug 16 '16 at 15:48
  • Whatever you are trying to do, this might rank up there as a bad precedent for your system – Drew Aug 16 '16 at 15:50
  • We are automating a certain report. Depending on the template feed into the procedure, a different query selection is used. Is there anything wrong with the syntax? If it's allowed to put expressions inside statements why it would say I have a syntax error? – WoofDg79 Aug 16 '16 at 17:05

2 Answers2

0

According to MySQL docs, nested CASE statements are supported, I tried your code in a test_proc and it works, the only thing what I change is table to table1 because table is a 

delimiter $$
create procedure test_proc()
begin
    CASE
        WHEN x = 1 THEN
            SELECT
                5 AS 'Col1',
                CASE
                    WHEN y = 5 THEN 2 
                    ELSE 0
                END AS 'Col2'
            FROM table1
            WHERE 1=1;
        WHEN x = 2 THEN
            SELECT
                *
            FROM table2
            ;
        ELSE
            SELECT
                *
            FROM table3
            ;
    END CASE;
end $$
delimiter ;
Ivan Cachicatari
  • 4,212
  • 2
  • 21
  • 41
  • Hmmm very interesting. Do you have any idea why my procedure would be throwing a syntax error? It fails to compile for me. Maybe it's the editor itself. – WoofDg79 Aug 16 '16 at 17:33
  • Found it!! It was my delimiter still being set to semi-colon. Once I changed it to $$ as you did it worked perfectly. Thank you! – WoofDg79 Aug 16 '16 at 17:35
  • Great, now you can close this question... regards :) – Ivan Cachicatari Aug 16 '16 at 17:39
0

It was my delimiter. If I change the delimiter for the general procedure to something other than the semi-colon and change it back in the end everything works fine.

WoofDg79
  • 131
  • 2
  • 7