如何在WHERE子句中使用CASE语句？存储过程场景需求咨询

Got it, let's sort out this stored procedure logic for you. The issue with your original code is that you're trying to use a CASE statement to return a conditional comparison (like code <> '2') directly in an equality check, which doesn't work the way you expect. Let me break this down and show you the correct approaches.

Why Your Original Code Isn't Working #

Your current WHERE clause:

code=case when request_type=1 then code else 4 end

When request_type=1, this simplifies to code = code — which is always true for every row (unless code is NULL). That's why it's not filtering out rows where code='2' like you want.

Correct Approach 1: Use Logical Operators (Most Readable) #

For simple conditional filtering, combining AND/OR is often clearer and easier for the database to optimize. Here's the updated stored procedure:

CREATE PROCEDURE test101 (IN request_type INT)
BEGIN
  SELECT code FROM property_master
  WHERE 
    -- When request_type is 1, keep rows where code isn't '2'
    (request_type = 1 AND code <> '2')
    -- For all other request_type values, keep rows where code is '4'
    OR 
    (request_type <> 1 AND code = '4');
END;

Correct Approach 2: Use CASE for Conditional Logic #

If you prefer using CASE (maybe for more complex multi-branch scenarios later), you can structure it to return a boolean-like result that the WHERE clause evaluates:

CREATE PROCEDURE test101 (IN request_type INT)
BEGIN
  SELECT code FROM property_master
  WHERE 
    CASE
      WHEN request_type = 1 THEN code <> '2' -- Returns TRUE/FALSE for this condition
      ELSE code = '4' -- Default case: match code='4'
    END;
END;

This works because most SQL databases treat boolean values (TRUE/FALSE) as 1/0 in context, so the WHERE clause will keep rows where the CASE result is TRUE.

Key Notes #

• If you need to handle NULL values for code, you might want to add checks (like code IS NOT NULL) depending on your database's behavior.
• The logical operator approach is generally preferred for simple conditions since it's more readable and often performs better.

内容的提问来源于stack exchange，提问作者Ritesh Fondke