21

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

Improve this question
1

3 Answers 3

Reset to default
50

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 ...

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

+1 This is the mechanism I teach to junior developers. It's well-optimised by SQL Server and is one of the easier forms of this logic to read.
Co-workers discourage me to use this approach because "the query will evaluate the same conditions for every row (e.g.: @what is null) and it will affect performance". Are they right? Tragedian, where I can find some proof of the "well-optimisation" you wrote about?
Yes, it is not the best performance wise solution. But the answer was the solution for the OP problem. You can handle this from the application side instead of doing this this way.
In case you were wondering (like me) if the WHERE 1=1 was in some form part of the solution, I don't believe it is. Its just for syntactical convenience to make all the where statements line up.
4

try something like

AND companies_SimpleList.CityId = @CityId or @CityID = 0
Improve this answer

3 Comments

Parentheses around the two ORed terms would be a good idea.
Agreed, bit slack of me that. I don't like depending on order of precedence either.
Just to be absolutely clear, I meant that in the context of the OP's query AND companies_SimpleList.CityId = @CityId or @CityID = 0 is quite different from AND ( companies_SimpleList.CityId = @CityId or @CityID = 0 ).
0

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) + '%'
Improve this answer

1 Comment

This should be avoided in favor of the accepted answer. see The Coalesce Trap

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.