Building dynamic WHERE clause in stored procedure

Question

I'm using SQL Server 2008 Express, and I have a stored procedure that do a SELECT from table, based on parameters. I have nvarchar parameters and int parameters.

Here is my problem, my where clause looks like this:

WHERE [companies_SimpleList].[Description] Like @What 
    AND companies_SimpleList.Keywords Like @Keywords
    AND companies_SimpleList.FullAdress Like @Where
    AND companies_SimpleList.ActivityId = @ActivityId
    AND companies_SimpleList.DepartementId = @DepartementId
    AND companies_SimpleList.CityId = @CityId

This parameters are the filter values set by the user of my ASP.NET MVC 3 application, and the int parameters may not be set, so their value will be 0. This is my problem, the stored procedure will search for items who have 0 as CityId for example, and for this, it return a wrong result. So it will be nice, to be able to have a dynamic where clause, based on if the value of int parameter is grater than 0, or not.

Thanks in advance

Answer 1

Try this instead:

WHERE 1 = 1
AND (@what     IS NULL OR [companies_SimpleList].[Description] Like @What )
AND (@keywords IS NULL OR companies_SimpleList.Keywords        Like @Keywords)
AND (@where    IS NULL OR companies_SimpleList.FullAdress      Like @Where)
...

If any of the parameters @what, @where is sent to the stored procedure with NULL value then the condition will be ignored. You can use 0 instead of null as a test value then it will be something like @what = 0 OR ...