在sql中使用And或Or获得多个结果

我有一个文本框和3个下拉框,每个下拉框中都可以选择一个项目。我希望能够实现两种过滤场景。

1. 在文本框中键入值后获得过滤结果,并在下两个文本框中选择值,忽略最后一个文本框。

2. 在文本框中键入值后获得过滤,并在其他三个下拉框中选择值以过滤结果。如果我将“and”改为“or”,并且将“or”改为”和“s”,则下面的代码一次只能生成一个结果。有人可以帮助修改或新查询以实现这两个场景吗?

CREATE PROC Spsearchproduct @searchWord1OnMasterPage NVARCHAR (50),
                            @searchWord2OnMasterPage NVARCHAR (50),
                            @searchWord3OnMasterPage NVARCHAR (50),
                            @searchWord4OnMasterPage NVARCHAR (50)
AS
  BEGIN
      SELECT product.NAME,
             price,
             seller,
             productstreetno.strno,
             productstreet.streetname
      FROM   product
             INNER JOIN productstreetno
                     ON product.streetnoid = productstreetno.idstreetno
             INNER JOIN productstreet
                     ON product.streetid = productstreet.idstreet
             INNER JOIN productstate
                     ON stateid = productstate.idstate
             INNER JOIN productcity
                     ON cityid = productcity.idcity
      WHERE  product.NAME LIKE '%' + @searchWord1OnMasterPage + '%'
             AND productstate.statename LIKE '%' + @searchWord2OnMasterPage + '%'
             AND ( ( productcity.cityname LIKE '%' + @searchWord3OnMasterPage + '%' )
                    OR ( productstreet.streetname LIKE '%' + @searchWord4OnMasterPage + '%' )
                    OR ( productstreet.streetname IS NULL ) )
             AND ( ( productcity.cityname LIKE '%' + @searchWord3OnMasterPage + '%' )
                    OR ( productstreet.streetname LIKE '%' + @searchWord4OnMasterPage + '%' )
                    OR ( productstreet.streetname IS NULL ) )
  END