CODEWITHSUNDEEP
mysqlsqlstored-procedures
Johnny000
Johnny000

Reputation: 2104

case when statement after select not working in stored procedure

I try to do something like this 

    SET parentid = (SELECT parent FROM pages WHERE id = pageid);
    CASE WHEN ( parentid <> 0 ) THEN
      SET mainid = (SELECT parent FROM pages WHERE id = parentid);
    ELSE 
      SET mainid = 0;
    END

But for some reason I get

Syntax error near 'CASE WHEN ( parentid <> 0 ) THEN SET mainid = (SELECT parent FROM pages WHERE ' at line 3

What am I missing?

Upvotes: 0

Views: 84

Answers (2)

krokodilko
krokodilko

Reputation: 36107

There is no END CASE clause and missing semicolon at the end:

CASE WHEN ( parentid <> 0 ) THEN
      SET mainid = (SELECT parent FROM pages WHERE id = parentid);
    ELSE 
      SET mainid = 0;
END CASE;

Please read documentation for proper syntax: http://dev.mysql.com/doc/refman/5.0/en/case.html
Here is link to a working demo: http://www.sqlfiddle.com/#!2/9495d1/2

Upvotes: 1

PlantTheIdea
PlantTheIdea

Reputation: 16369

I feel like this doesn't need variables...

SELECT CAST(CASE
    WHEN id = pageid AND parent = 0
    THEN 0
    ELSE (SELECT parent FROM pages WHERE id = parentid)
END AS INTEGER) AS mainid
FROM pages

Upvotes: 0

Related Questions