Disregard some parameters

[Gunnar Liknes]

Hi,

I am building a search Query which takes 7 parameters:

Lets call them @P1 .. @P7 (all Int's)

The query is a simple select query like:

SELECT * FROM MyTable WHERE (Field1 = @P1) AND (Field2 = @P2) ....

My problem is that if some parameters are -1 they shall be disregarded.

Is there any way to set a parameter to a value meaning "Anything", or do I
have to
remove the criteria from the select clause?

(In the latter case I cannot use a stored procedure, which is what I prefer)

Cheers
Gunnar

[Gert-Jan Strik]

Re: Disregard some parameters

Gunnar,

if your columns do not contain NULLs, then you can use

WHERE Field1 BETWEEN COALESCE(NULLIF (@P1,-1),-2147483648) AND
COALESCE(NULLIF (@P1,-1),2147483647)

This WHERE clause assumes that the value "-1" is your indication of a
missing parameter. If you use NULL instead of -1, then you can replace
NULLIF(@P1,-1) with @P1. It also assumes the int datatype. If the column
is of a different integer datatype (for example bigint), then the
minimum and maximum value need to be adjusted.

If your column do contain NULLs, then you can use

WHERE CASE WHEN @P1=-1 THEN 1
CASE WHEN Field1=@P1 THEN 1
ELSE 0 END = 1

But in general, the first approach will perform better.

Hope this helps,
Gert-Jan


Gunnar Liknes wrote:[color=blue]
>
> Hi,
>
> I am building a search Query which takes 7 parameters:
>
> Lets call them @P1 .. @P7 (all Int's)
>
> The query is a simple select query like:
>
> SELECT * FROM MyTable WHERE (Field1 = @P1) AND (Field2 = @P2) ....
>
> My problem is that if some parameters are -1 they shall be disregarded.
>
> Is there any way to set a parameter to a value meaning "Anything", or do I
> have to
> remove the criteria from the select clause?
>
> (In the latter case I cannot use a stored procedure, which is what I prefer)
>
> Cheers
> Gunnar[/color]

--
(Please reply only to the newsgroup)

[Jørgen Lei]

Re: Disregard some parameters

Hi,

One way to do the trick is to write:

SELECT * FROM MyTable WHERE ((@P1 = -1) OR (Field1 = @P1)) AND ...

If @P1 is -1, the OR-clause will simply be true for all rows, effectively
ignoring the comparison with Field1.

-Jørgen

"Gunnar Liknes" <g_liknes.Tabor tunderscores@g_ lobal-satcom.com> skrev i en
meddelelse news:4110a27d$1 @news.broadpark .no...[color=blue]
> Hi,
>
> I am building a search Query which takes 7 parameters:
>
> Lets call them @P1 .. @P7 (all Int's)
>
> The query is a simple select query like:
>
> SELECT * FROM MyTable WHERE (Field1 = @P1) AND (Field2 = @P2) ....
>
> My problem is that if some parameters are -1 they shall be disregarded.
>
> Is there any way to set a parameter to a value meaning "Anything", or do I
> have to
> remove the criteria from the select clause?
>
> (In the latter case I cannot use a stored procedure, which is what I[/color]
prefer)[color=blue]
>
> Cheers
> Gunnar
>
>
>
>[/color]

[Gunnar Liknes]

Re: Disregard some parameters

"Gert-Jan Strik" wrote
[color=blue]
> if your columns do not contain NULLs, then you can use
>
> WHERE Field1 BETWEEN COALESCE(NULLIF (@P1,-1),-2147483648) AND
> COALESCE(NULLIF (@P1,-1),2147483647)
>
> This WHERE clause assumes that the value "-1" is your indication of a
> missing parameter. If you use NULL instead of -1, then you can replace
> NULLIF(@P1,-1) with @P1. It also assumes the int datatype. If the column
> is of a different integer datatype (for example bigint), then the
> minimum and maximum value need to be adjusted.
>
> If your column do contain NULLs, then you can use
>
> WHERE CASE WHEN @P1=-1 THEN 1
> CASE WHEN Field1=@P1 THEN 1
> ELSE 0 END = 1
>
> But in general, the first approach will perform better.[/color]

Thank you both (Gert-Jan and Jørgen) for two excellent working solutions to
my problem. The COALESCE function was interesting. Will it perform better
than the "WHERE ((@P1 = -1) OR (Field1 = @P1)) "
approach?

Thanks,
Gunnar

[Gert-Jan Strik]

Re: Disregard some parameters

Gunnar Liknes wrote:[color=blue]
> Thank you both (Gert-Jan and Jørgen) for two excellent working solutions to
> my problem. The COALESCE function was interesting. Will it perform better
> than the "WHERE ((@P1 = -1) OR (Field1 = @P1)) "
> approach?
>
> Thanks,
> Gunnar[/color]

Yes, because if the column is indexed, index seeks can be used. The OR
solution needs an index scan.

Gert-Jan
--
(Please reply only to the newsgroup)

[Erland Sommarskog]

Re: Disregard some parameters

Gunnar Liknes (g_liknes.Tabor tunderscores@g_ lobal-satcom.com) writes:[color=blue]
> Thank you both (Gert-Jan and Jørgen) for two excellent working solutions
> to my problem. The COALESCE function was interesting. Will it perform
> better than the "WHERE ((@P1 = -1) OR (Field1 = @P1)) " approach?[/color]

Permit me to modify Gert-Jan's enthusiasm a little. It may perform better,
but I have not always be successful with it. And if the columns is not
indexed then it not matter much anyway.

The only way to find out is to benchmark.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Gunnar Liknes]

Re: Disregard some parameters

"Gert-Jan Strik" wrote[color=blue]
> Gunnar Liknes wrote:[color=green]
> > Thank you both (Gert-Jan and Jørgen) for two excellent working solutions[/color][/color]
to[color=blue][color=green]
> > my problem. The COALESCE function was interesting. Will it perform[/color][/color]
better[color=blue][color=green]
> > than the "WHERE ((@P1 = -1) OR (Field1 = @P1)) "
> > approach?[/color][/color]
[color=blue]
> Yes, because if the column is indexed, index seeks can be used. The OR
> solution needs an index scan.[/color]

Does MS SQL perform complete boolean evaluations? If (@P1=-1) it should
not have to check if (Field1 = @P1) because the result of the statement is
already determined.

Gunnar

[Erland Sommarskog]

Re: Disregard some parameters

Gunnar Liknes (g_liknes.Tabor tunderscores@g_ lobal-satcom.com) writes:[color=blue]
> Does MS SQL perform complete boolean evaluations? If (@P1=-1) it should
> not have to check if (Field1 = @P1) because the result of the statement is
> already determined.[/color]

The answer is that, yes, SQL Server is able to make logical shortcuts,
but that is not applicable here.

When SQL Server builds a query plan for a stored procedure, it builds
the plan for the entire procedure at once, and is thus blind to what
the actual values of variables and parameters at the time of the statement.
It does take in regard the values of parameter to build the plan, but
since it don't know whether parameter changes value in the procedure or
not, SQL Server can choose a plan which would yield the wrong result if
the parameter is changed. Moreover, since the plan is cached, the procedure
might be called with some other values the next time.

Thus if you have:

SELECT *
FROM tbl
WHERE (field1 = @p1 OR @p1 IS NULL)
AND (field2 = @p2 OR @p2 IS NULL)

It cannot look at @p1 and say "Hey @p1 is NULL, I don't have to test
Field1". So it must pick a plan where it accesses field1. No, once it
comes to the statement it could opt to not actually check field1, but
the cost is not the check - the cost is the access. In this case,
the optimizer will most like to scan the table from left to right.

Here is another example:

SELECT *
FROM tbl
WHERE @p1 = 0 OR EXISTS (SELECT *
FROM tbl2
WHERE tbl.col = tbl2.col)

Here, if @p1 is 0 we retrieve all rows from tbl, but if @p1 is 1 only
rows which has a matching row in tbl2 are to be returned. In this example,
SQL Server is actually able to avoid accessing tbl2 if @p1 is 0, since
once @p1 is evaluated, the other branch can be pruned. Note here that
is not your C-style of shortcutting - you get the same result if you
have the condition on @p1 last.

To read more about this topic, I have an article on my web site:
http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Gunnar Liknes]

Re: Disregard some parameters

"Erland Sommarskog" wrote.[color=blue]
> Gunnar Liknes writes:[/color]
[color=blue][color=green]
> > Does MS SQL perform complete boolean evaluations? If (@P1=-1) it should
> > not have to check if (Field1 = @P1) because the result of the statement[/color][/color]
is[color=blue][color=green]
> > already determined.[/color][/color]
[color=blue]
> The answer is that, yes, SQL Server is able to make logical shortcuts,
> but that is not applicable here.[/color]

<snip explanation>
[color=blue]
> To read more about this topic, I have an article on my web site:
> http://www.sommarskog.se/dyn-search.html.[/color]

Thank you Erland, your article was very helpful. I also found the topics of
your
other articles very interresting. I'll read the one about Arrays & Lists
when I have
some time.

Regarding search. I'll have to wait until we get more data in our database
before
I decide which search alternative to use. For now I stick to IF / OR.

Cheers
Gunnar