MYSQL stored procedure, case

I've consulted this page: http://dev.mysql.com/doc/refman/5.1/en/case.html as well as this one, but can't get a simple procedure to work....

UPDATE: To clearify what I want to do: I want to select all rows from a table where the field id is either 1, 0 or could be either of them. This is specified by an input parameter to the procedure, which takes values 0,1 or 2.

So if _id = 0 I want: select * from TABLE where id = 0

If _id = 1 I want: select * from TABLE where id = 1

And if _id = 2 I want: select * from TABLE where id in (0,1)

I was hoping I could get the rest of it to work by myself if I only got the simple case-statement below to work...

What I want to do is something like:

begin
    select * from TABLE where
        case _id
        when  0 then id=0
        else id = 1
        end as id
end

which gives error "You have an error in your SQL syntax".

I've also tried:

begin
    select * from TABLE where
        case _id
        when  0 then id=0
        else id=1
        end case
end

which gives the same error. Obviously I've got the wrong syntax somewhere, but I can't figure out where... Can anyone help me out?

Thanks, Niklas

回答1:

Try this:

begin
    select *,
        case _id
        when 0 then 0
        else 1
        end as id
    from table
end

When used as part of a SELECT query, WHEN is not a statement, it's a control flow function.

You can also express this as:

begin
    select *, _id != 0 as id
    from table
end

回答2:

build the query to look like this... mySQL case have a different syntax from C and Java...

set @input=2;

select * from foo 
 where 
     case when @input =2 then  value in ( 1, 0)  else
       value =  @input end;

live demo with sql fidle