基本上这样做

  SELECT  * 
 FROM 员工
 WHERE 电子邮件=  @ Email  或  COALESCE （ @ Email ，' '）= ' ' 

如果没有通过电子邮件，那么或将负责处理。e>

 选择 
 * 
来自 employee 
其中 
 Name = isnull（ @ Name  ，名称）
和年龄= isnull（ @年龄，年龄）
和 email = isnull（ @ Email ，email）
和性别= isnull（ @ Gender ，性别）
或 

选择 
 * 
来自员工
其中 
（ @ Name     null  或名称=  @ Name ）
和（ @ Age     null  或年龄=  @ Age ）
和（ @ Email     null  或 email =  @ Email ）
和（ @ Gender     null  或性别=  @性别）

- 使用动态sql的第二种解决方案

   -    exec SearchEmployee @ Name ='anurag'，@ Age = 23  
  -    exec SearchEmployee @ Age = 23  
 alter   proc  SearchEmployee   -   ''， ''，''，'' 
 @ Name   varchar （ 50 ）= null，
 @ Age  int = null 
 as  
开始 

声明  @sql   varchar （max）， @ sqlwhere   varchar （max）

 set  @sql =   select * from employee' 

 if （（ @ Name    不  null ）和  @ Name <> ' '）
开始 
 set  @sqlwhere = '  Name ='  + ' ''' +  @ Name  + ' ''' 
 print  '  sqlwhere' 
结束 

 if （（ @ Age    不  null ）和  @ Age <> ' '）
 b egin  
 print  '  im在年龄块' 
 set   @ sqlwhere  = isnull（ @ sqlwhere  + ' 和'，' '）+ ' 年龄=''' + cast（ @ Age   as   varchar （ 50 ））+ ' '''; 
 print  '  sqlwhere' + cast（ @ sqlwhere   as   varchar （  50 ））
 end  



 if （ @ sqlwhere     not   null ）
开始 
 set  @sql = @sql + '  where'  +  @ sqlwhere ; 
 end  
 else  
开始 
 set  @sql = @sql; 
 end  

 print   @sql  
 exec （ @ sql ）

结束 

--if no parameter is passed the sp should return all the result. 
--if i pass any one parameter then value should display as per the parameter
--i tried this. i think i am doing correct but if i execute 
--SearchEmployee '','','',''
--i get error 

select * from employee where  Name=
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.

--why i get this error ...as i understand all the parameter are '' . so why i am getting like this select * from employee where  and Gender=

=======================================stored procedure======================================

alter proc SearchEmployee '','','',''
@Name varchar(50)=null,
@Age int=null,
@Gender varchar(50)=null,
@Email varchar(50)=null
as
begin
 
declare @sql varchar(max),@sqlwhere varchar(max)
set @sqlwhere=''; 
 
set @sql='select * from employee' 
 
if ((@Name is not null) or @Name <> '')
begin
set @sqlwhere=' Name='+ @Name 
end

 
else if ((@Age is not null) or @Age <> '')
begin
set @sqlwhere='and Age='+ cast(@Age as varchar(50)) 
end

 
else if ((@Email is not null) or @Email <> '')
begin
set @sqlwhere=' and email='+ @Email
end

 

else if ((@Gender is not null) or @Gender <> '')
begin
set @sqlwhere=' and Gender='+ @Gender
end

 
if (@sqlwhere <> '')
begin
set @sql=@sql+' where ' + @sqlwhere;
end
else
begin
set @sql=@sql;
end

print @sql
exec (@sql) 
 
end

------------------------------------------------------------------

employee table

Name	Age	Gender	email
anurag	24	Male	anu@gmail.com
abhi	22	Male	abhi@gmail.com
ruchi	23	Female	ruchi@gmail.com
siba	24	Male	siba@gmail.com
mukua	24	Male	mukua@gmail.com
prachi	24	Female	prachi@gmail.com
preeti	24	Female	preeti@gmail.com


--executing 
SearchEmployee '','','',''

this should give me all the result.

--executing the below should give me all the employees who are 24 years
SearchEmployee '','24','',''

----------------as suggested i changed from or to and and tried again-----------

SearchEmployee '','','','' --this gave me all the result

but when i tried the below

SearchEmployee 'anurag','','',''

i got error

select * from employee where  Name=anurag
Msg 207, Level 16, State 1, Line 1
Invalid column name 'anurag'.

Essentially do this

SELECT * 
FROM Employee
WHERE Email = @Email OR COALESCE(@Email, '') = ''

If no email is passed in then the or will take care of it.

select
    *
from employee
where
    Name = isnull(@Name, Name)
    and Age = isnull(@Age, Age)
    and email = isnull(@Email, email)
    and Gender = isnull(@Gender, Gender)
or

select
    *
from employee
where
    (@Name is null or Name = @Name)
    and (@Age is null or Age = @Age)
    and (@Email is null or email = @Email)
    and (@Gender is null or Gender = @Gender)

--second solution using dynamic sql

--exec SearchEmployee @Name='anurag',@Age=23 
--exec SearchEmployee @Age=23 
alter proc SearchEmployee-- '','','',''
@Name varchar(50)=null,
@Age int=null
as
begin
 
declare @sql varchar(max),@sqlwhere varchar(max)
  
set @sql='select * from employee' 
 
if ((@Name is not null) and @Name <> '')
begin
set @sqlwhere=' Name='+ '''' + @Name + ''''
print 'sqlwhere '
end
 
 if ((@Age is not null) and @Age <> '')
begin
 print 'i m in age block '
    set @sqlwhere =isnull(@sqlwhere + ' and ', '') + ' Age = ''' + cast(@Age as varchar(50)) + ''' ';
print 'sqlwhere ' + cast(@sqlwhere as varchar(50))
end
 

 
if (@sqlwhere is not null)
begin
set @sql=@sql+' where ' + @sqlwhere;
end
else
begin
set @sql=@sql;
end
 
print @sql
exec (@sql) 
 
end