搜索基于两个条件文本框和下拉列表？

Question

    alter PROCEDURE K_HM_SearchFarmeragentDet
@Type varchar(50),@farmername varchar(50)   
AS
BEGIN

    SET NOCOUNT ON;
SELECT MHA.sno,MHA.farmername,MHA.referencedby,HMB.Branch,MHA.area,MHA.contactno,MHA.Type from k_Master_AddHatchingFarmer MHA inner join
 K_HM_Branch HMB on MHA.Branch=HMB.Sno where farmername like @farmername+'%'  order by farmername

END
GO

我已经编写了这样的过程，用于搜索基于farmername的数据，但是我想要搜索基于farmername的数据，以及一个类型下拉列表。类型包含两个值“农民”和“代理”。如果我只想找农民，我怎么写程序请帮我.

Answer 1

  alter PROCEDURE K_HM_SearchFarmeragentDet
  @Type varchar(50),@farmername varchar(50)   
  AS
  BEGIN

  SET NOCOUNT ON;
  SELECT MHA.sno,MHA.farmername,MHA.referencedby,HMB.Branch,MHA.area,MHA.contactno,MHA.Type from k_Master_AddHatchingFarmer MHA inner join
  K_HM_Branch HMB on MHA.Branch=HMB.Sno where farmername like @farmername+'%' and    Type=@Type order by farmername

  END
  GO

尝尝这个

Answer 2

尝试这样做，如果没有从下拉列表中选择任何内容，请确保将空值传递给存储过程。

ALTER PROCEDURE K_HM_SearchFarmeragentDet
@Type varchar(50),@farmername varchar(50)   
AS
BEGIN
SET NOCOUNT ON;
SELECT MHA.sno,MHA.farmername,MHA.referencedby,HMB.Branch,MHA.area,MHA.contactno,MHA.Type from k_Master_AddHatchingFarmer MHA inner join
 K_HM_Branch HMB on MHA.Branch=HMB.Sno 
where farmername like @farmername+'%'  
AND MHA.Type = ISNULL(@Type, MHA.Type)
order by farmername
END
GO