subquery - How can I filter with a derived table in MS SQL Server? -

sql server management studio 2005

i wrote query allow user search on various categories such description, country of origin, brand name, , grow method. user able input data many filters , results returned based on inputted.

below working version of query; how ssms reformatted original query wrote before. need try , figure out how wrote before can add 2 more columns filter:

    select distinct mastercorporategtin.supplierno, mastercorporategtin.gtin, mastercorporategtin.description, mastercorporategtin.commodity, mastercorporategtin.variety, mastercorporategtin.coo, mastercorporategtin.packsize, mastercorporategtin.brandname, mastercorporategtin.growmethod, mastercorporategtin.grade             gtins mastercorporategtin inner join         (select distinct supplierno, gtin, description, coo, packsize, brandname, growmethod, grade                  gtins mastercorporategtin              (description '%' + @productdescription + '%') , (brandname '%' + @brandname + '%') ,         (growmethod '%' + @growmethod + '%') , (coo = @countryoforigin) or         (description '%' + @productdescription + '%') , (brandname '%' + @brandname + '%') , (coo = @countryoforigin) ,         (@growmethod null) or         (description '%' + @productdescription + '%') , (brandname '%' + @brandname + '%') ,         (growmethod '%' + @growmethod + '%') , (@countryoforigin null) or         (description '%' + @productdescription + '%') , (brandname '%' + @brandname + '%') , (@growmethod null) ,         (@countryoforigin null) or         (description '%' + @productdescription + '%') , (growmethod '%' + @growmethod + '%') , (coo = @countryoforigin) ,         (@brandname null) or         (description '%' + @productdescription + '%') , (coo = @countryoforigin) , (@growmethod null) ,         (@brandname null) or         (description '%' + @productdescription + '%') , (growmethod '%' + @growmethod + '%') , (@countryoforigin null) ,         (@brandname null) or         (description '%' + @productdescription + '%') , (@growmethod null) , (@countryoforigin null) ,         (@brandname null) or         (brandname '%' + @brandname + '%') , (growmethod '%' + @growmethod + '%') , (coo = @countryoforigin) ,         (@productdescription null) or         (brandname '%' + @brandname + '%') , (coo = @countryoforigin) , (@growmethod null) ,         (@productdescription null) or         (brandname '%' + @brandname + '%') , (growmethod '%' + @growmethod + '%') , (@countryoforigin null) ,         (@productdescription null) or         (brandname '%' + @brandname + '%') , (@growmethod null) , (@countryoforigin null) ,         (@productdescription null) or         (growmethod '%' + @growmethod + '%') , (coo = @countryoforigin) , (@brandname null) ,         (@productdescription null) or         (growmethod '%' + @growmethod + '%') , (@countryoforigin null) , (@brandname null) ,         (@productdescription null) or         (coo = @countryoforigin) , (@growmethod null) , (@brandname null) , (@productdescription null) or         (@growmethod null) , (@countryoforigin null) , (@brandname null) , (@productdescription null))         productfilter on mastercorporategtin.description = productfilter.description , mastercorporategtin.brandname = productfilter.brandname ,         mastercorporategtin.growmethod = productfilter.growmethod , mastercorporategtin.coo = productfilter.coo 

from can remember did before similar below. think kept adding statements in clause 1 one see if worked. i've tried again didn't work. i'd appreciate insight. thank you.

    select distinct mastercorporategtin.supplierno, mastercorporategtin.gtin,         mastercorporategtin.description, mastercorporategtin.commodity,         mastercorporategtin.variety, mastercorporategtin.coo, mastercorporategtin.packsize,         mastercorporategtin.brandname, mastercorporategtin.growmethod, mastercorporategtin.grade             gtins mastercorporategtin inner join     (select distinct supplierno, gtin, description, commodity, packsize, brandname,         growmethod, grade              gtins mastercorporategtin          (description '%' + @productdescription + '%') or     (@productdescription null)) searchfilter on mastercorporategtin.description =         searchfilter.description 

something seems off parentheses... you'd such:

((@somefield null) or (somefield = @somefield)) 

for example, if i'm reading query correctly, if @brandname null, results return. there seems lot of redundant statements in query , missing outer parentheses group statements.