CREATE DEFINER=`root`@`%` PROCEDURE `getHousing`(in idNumber VARCHAR(32),in unitName VARCHAR(32),in userName VARCHAR(32),in bankAccount VARCHAR(32),in houseStartTime VARCHAR(32),in houseEndTime VARCHAR(32),in confirmStatus VARCHAR(32))
BEGIN
#Routine body goes here...
SET @sql = NULL;
#身份证号码
SET @idNumberHave = '';
#单位名称
SET @unitName = '';
#姓名
SET @userName = '';
#银行账号
SET @bankAccount = '';
#租房开始时间
SET @houseStartTime = '';
#租房结束时间
SET @houseEndTime = '';
#报销状态
SET @confirmStatus = '';
# 动态拼接每年的住房补助备注信息
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(hb.particular_year = ''',
hb.particular_year,
''', hb.house_remarks, 0)) AS ''',
hb.particular_year, ''''
)
)INTO @sql
FROM dy_housing_backup hb;
# 拼接身份证号码
if idNumber is not null and idNumber != '' then
SET @idNumberHave = CONCAT(' and h.id_number =',idNumber);
END if;
# 拼接用户名
if unitName is not null and unitName != '' then
set @unitName = CONCAT(' and h.unit_name =',unitName);
END if;
# 拼接用户名
if userName is not null and userName!='' then
set @userName = CONCAT(' and h.user_name =',userName);
END if;
# 拼接身份证号
if bankAccount is not null and bankAccount !='' then
set @bankAccount = CONCAT(' and h.bank_account =',bankAccount);
END if;
# 拼接住房开始时间
if houseStartTime is not null and houseStartTime!='' then
set @houseStartTime = CONCAT(' and h.house_start_time =',houseStartTime);
END if;
# 拼接住房结束时间
if houseEndTime is not null and houseEndTime!='' then
set @houseEndTime = CONCAT(' and h.house_end_time =',houseEndTime);
END if;
# 拼接报销状态
if confirmStatus is not null and confirmStatus!='' then
set @confirmStatus = CONCAT(' and h.confirm_status =',confirmStatus);
else
set @confirmStatus = CONCAT(' and h.confirm_status = 0');
END if;
# sql 拼接
SET @sql = CONCAT('Select h.id,
h.create_by,
h.create_time,
h.update_by,
h.update_time,
h.confirm_status,
j.army_duty_type ,
j.army_age ,
j.join_date,', @sql,
' FROM dy_housing h
LEFT JOIN dy_backup hb on hb.dy_housing_id = h.id AND h.id_number = hb.id_number
LEFT JOIN db_jb j on h.id_number = j.id_number
where 1=1 ',@idNumberHave,@unitName, @userName,@bankAccount,@houseStartTime ,@houseEndTime ,@confirmStatus ,'
GROUP BY h.id_number');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
需要注意的是,在测试传值的过程中,如果是字符串类型要添加双引号或单引号