如何设置带IN参数的存储过程，将空值视为全量查询指令

我编写了如下存储过程：

CREATE DEFINER=`root`@`localhost` PROCEDURE `find_stock_order`(
    in_product_name varchar(45),
    in_date DATE)
BEGIN
    SELECT * from stock_order_view
    where product_name=in_product_name and DateTime_order=in_date;
END

当两个参数都传入有效值时，存储过程能正常运行。但如果只想按product_name查询所有订单，不指定in_date参数（希望空值代表查询所有日期）时，会因DATE类型参数不接受空字符串而报错。

示例数据表：

传入参数：

• in_product_name: Burger
• in_date: 留空（自动识别为''）

期望结果：

但实际因DATE参数格式要求报错。

要解决这个问题，需从两方面调整：

1. 给in_date参数设置默认值为NULL，DATE类型支持NULL值，而非空字符串
2. 修改WHERE条件，当in_date为NULL时，跳过日期匹配逻辑

修改后的存储过程代码：

CREATE DEFINER=`root`@`localhost` PROCEDURE `find_stock_order`(
    in_product_name varchar(45),
    in_date DATE DEFAULT NULL) -- 设置默认值为NULL
BEGIN
    SELECT * from stock_order_view
    where product_name = in_product_name
      -- 当in_date不为NULL时才匹配日期，否则忽略该条件
      AND (in_date IS NULL OR DateTime_order = in_date);
END

调用方式 #

• 需要指定日期时，正常传入DATE格式的值：

  CALL find_stock_order('Burger', '2022-12-10');
  

• 不需要指定日期时，可选择不传该参数或显式传入NULL：

  -- 方式1：不传in_date参数，使用默认值NULL
  CALL find_stock_order('Burger');
  -- 方式2：显式传入NULL
  CALL find_stock_order('Burger', NULL);
  

这样就能实现需求：指定日期时过滤对应日期的记录，不指定日期时返回该产品的所有订单。

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