Reputation: 2352
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
Upvotes: 20
Views: 43360
Reputation: 79889
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 ...
Upvotes: 49
Reputation: 2902
Here is another easy-to-read solution for SQL Server >=2008
CREATE PROCEDURE FindEmployee
@Id INT = NULL,
@SecondName NVARCHAR(MAX) = NULL
AS
SELECT Employees.Id AS "Id",
Employees.FirstName AS "FirstName",
Employees.SecondName AS "SecondName"
FROM Employees
WHERE Employees.Id = COALESCE(@Id, Employees.Id)
AND Employees.SecondName LIKE COALESCE(@SecondName, Employees.SecondName) + '%'
Upvotes: 0
Reputation: 20320
try something like
AND companies_SimpleList.CityId = @CityId or @CityID = 0
Upvotes: 4