CODEWITHSUNDEEP
mysql
smarble
smarble

Reputation: 335

Trouble understanding a Case Statement

I Have a case statement something like this:

CASE
  WHEN purpose = "" THEN SET @sPurpose = "%%"
  WHEN purpose IS NULL THEN SET @sPurpose = "%%"
  ELSE SET @sPurpose = concat("%",purpose,"%");
END;

but when i try to compile the procedure, it throws this error: 

SQL Error: 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 
'WHEN purpose IS NULL THEN SET @sPurpose = "%%" ELSE SET @sPurpose = co' at line 13

Am i just missing some syntactical thing?

Upvotes: 0

Views: 51

Answers (1)

John Woo
John Woo

Reputation: 263803

Here's the correct way to do that,

SET @sPurpose = CASE
                     WHEN purpose = "" THEN  "%%"
                     WHEN purpose IS NULL THEN  "%%"
                     ELSE  concat("%",purpose,"%")
                END;

Upvotes: 4

Related Questions