经常写重复性的sql，所以写了一个存储过程查询数据

-- 查询单个人员任职信息
CREATE DEFINER=`root`@`localhost` PROCEDURE `SingleStaffInfo`(
  IN sid VARCHAR ( 36 ),
  cname VARCHAR ( 100 ),
  ccode VARCHAR ( 100 ),
  tenantid VARCHAR ( 36 ),
  mobile VARCHAR ( 50 ),
  user_id VARCHAR ( 36 ),
  objid VARCHAR ( 36 ) 
  )
BEGIN
  
  SET @staffid = (
    IF
      (
        @sid <> '',
        @sid,
        (
        SELECT
          id 
        FROM
          bd_staff 
        WHERE
          1 = 1 
          AND ( CASE WHEN @cname <> '' THEN NAME = @cname ELSE 1 = 1 END ) 
          AND ( CASE WHEN @ccode <> '' THEN CODE = @ccode ELSE 1 = 1 END ) 
          AND ( CASE WHEN @tenantid <> '' THEN tenantid = @tenantid ELSE 1 = 1 END ) 
          AND ( CASE WHEN @mobile <> '' THEN mobile = @mobile ELSE 1 = 1 END ) 
          AND ( CASE WHEN @user_id <> '' THEN user_id = @user_id ELSE 1 = 1 END ) 
          AND ( CASE WHEN @objid <> '' THEN objid = @objid ELSE 1 = 1 END ) 
        ) 
      ) 
    );
  SELECT
    * 
  FROM
    bd_staff 
  WHERE
    id = @staffid;
  SELECT
    * 
  FROM
    bd_staff_job 
  WHERE
    staff_id = @staffid;
  SELECT
    * 
  FROM
    bd_staff_mainjob 
  WHERE
    staff_id = @staffid;

END

涉及语法

存储过程

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
 
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
 
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
 
routine_body:
　　Valid SQL routine statement
 
[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

IF表达式

IF ( expr1, expr2, expr3 );
----------------------
IF
  expr1 THEN
  expr2 [ ELSE expr3 ] 
  END IF;

case表达式

CASE var1 
WHEN var2 THEN expr1;
[ WHEN var3 THEN expr2;]
[ ELSE expr3;] 
END [ CASE ] 
-- ------------------ 
CASE
  WHEN expr1 THEN expr2;
[ WHEN expr3 THEN expr4;]
[ ELSE expr5;] 
END [ CASE]