sql - 如果所有/一个参数为空？

Question

create Procedure Proc_selectEmp
(
@name varchar(10) = null,
@lname varchar(10) = null,
@id varchar(10) = null

)
as
begin

select * from Emp
where 
(@name is null or name = @name) 
and (@lname is null or lname = @lname) 
and (@id is null or id = @id)
end

我的基本查询:select * from Emp。如果用户输入名称，它将被添加并且查询将是:

select *  
from Emp 
where name = txtname.Text

这是我的sp。它处理得当。

是否有使用 isnull() 和 case 的任何其他选项。

Answer 1

怎么样

where   ISNULL(@name,name) = name  
and     ISNULL(@lname,lname) = lname
and     ISNULL(@id,id) = id