I have a report that a user will need to put one or all of the search criteria in. Right now I have to enter all the search criteria for the report to generate any results.. Does anyone know of any good tutorials that might help? Thanks in advance!! SELECT a.[Serial No_], a.[Bit Size], a.[Bit Type], b.[Asset Location], b.[District], c.[Document Date] FROM [Ulterra Drilling Technologies$Asset] AS a JOIN [Ulterra Drilling Technologies$Asset Location Entry] AS b ON a.[Serial No_] = b.[Asset Serial No_] JOIN [Ulterra Drilling Technologies$Job Ledger Entry] as c ON a.[Serial No_] = c.[Serial No_] WHERE (b.[Current Location] = 1) and a.[Serial No_]=@serialNo and a.[Bit Size]=@bitSize and a.[Bit Type]=@bitType and b.[Asset Location]=@assetLoc and b.[District]=@dist and c.[Document Date]=@docDate

To make parameters optional, set the parameter properties to allow NULL and then change your where clause as follows: WHERE (b.[Current Location] = 1) and (a.[Serial No_]=@serialNo or @serialNo is null) and (a.[Bit Size]=@bitSize or @bitSize is null) etc... Craig Bryden - Please mark correct answers

Craig, The first part of advice is good, but the second may lead to performance problems. Take a look at the following blog posts explaining why and suggesting solutions. Do you use ISNULL(...). Don't, it does not perform - short blog by Denis Gobo Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later) - long and comprehensive article by Erland Sommarskog Catch All Queries - short blog by Gail Shaw Sunday T-SQL tip: How to select data with unknown parameter set Nice blog by Dmitri KorotkevitchFor every expert, there is an equal and opposite expert. - Becker's Law My blog

Thanks Naomi I haven't done this in so long I forgot about that issue. Thanks for pointing it out.Craig Bryden - Please mark correct answers

Hi SBoltan, You can use the query like this and check the Allow Null property of parameter true. WHERE (b.[Current Location] = 1) and (a.[Serial No_]=COALESCE(@serialNo,a.[Serial No_])) and (a.[Bit Size]=COALESCE(@bitSize,a.[Bit Size])) etc... Amar Deep Singh

Great Advice!! this works great!!