SQL语句如何处理where子句中变量为null的情况

Question

表 t_head	表 t_detail
head_id	head_id
head_name	detail_id
	detail_name

declare @detail_name nvarchar(20)

-- 这种情况当 @detail_name为null且t_detail表中无关联行时，查不到数据
select h.* from t_head h
left join t_detail d on d.head_id = h.head_id
where detail_name = isnull(@detail_name, detail_name)

-- 这种情况当 @detail_name为null时，只能查出t_head中与t_detail无关联行的数据
select h.* from t_head h
left join t_detail d on d.head_id = h.head_id
where detail_name is null or (detail_name is not null and detail_name = @detail_name)

我希望当 @detail_name为null时查出t_head中所有数据行，当 @detail_name不为null时根据条件查出对应数据行，能不能一条sql就满足以上要求？

Answer 1

where条件中用CASE WHEN 即可，当参数为空时detail_name = xxx写一个恒真等式，不为空时detail_name = 参数

declare @detail_name nvarchar(20)

select h.* from t_head h
left join t_detail d on d.head_id = h.head_id
where detail_name = case when isnull(@detail_name,'')='' then detail_name else @detail_name end