Parameterized Query issue

  • Thread starter Thread starter Steve Enzer
  • Start date Start date
S

Steve Enzer

Hi,

Using a data adapter, I've created a query containing four parameters. The
query looks something like this:

SELECT * FROM Materials WHERE (Type=@Type) AND (Material=@Material)

(I've simplified the statement to include only two parameters here.)

In using the query, I want the option of not setting specific values for all
the parameters. Is there any way to set a parameter to the equivalent of a
wildcard character that will allow all values of that parameter to be
accepted?

Thanks,
Steve Enzer
 
Steve - if I understand you correctly you can use

WHERE Type = @Type or @Type IS NULL
 
Actually, what I'm trying to do is create an option not to use a particular
parameter in a query. For example, if the user does not want to filter the
result by entering a value for @Type, I would like a simple method for
setting @Type so it doesn't have any effect on the query.

If I was just dealing with two parameters, it would be easy to just create
three different query strings, depending on one or both of the parameters.
But in the actual application I'm working on, I have five parameters, which
may or may not be used in any combination for creating the query. So I
would like to end up with one query string and the ability to use (or not
use) any combination of parameters.

I hope this clarifies what I'm trying to do.
 
Steve Enzer said:
Actually, what I'm trying to do is create an option not to use a
particular parameter in a query. For example, if the user does not want
to filter the result by entering a value for @Type, I would like a simple
method for setting @Type so it doesn't have any effect on the query.

If I was just dealing with two parameters, it would be easy to just create
three different query strings, depending on one or both of the parameters.
But in the actual application I'm working on, I have five parameters,
which may or may not be used in any combination for creating the query.
So I would like to end up with one query string and the ability to use (or
not use) any combination of parameters.

I hope this clarifies what I'm trying to do.
Click to expand...

You don't really want to do this. You may be saving yourself a few lines of
code, but you might be killing your SQL Server. If a user doesn't want to
filter on a column, then it shouldn't appear in the query.

These queries are different, and each should have its own query plan in SQL
Server.

SELECT * FROM Materials WHERE (Type=@Type) AND (Material=@Material)

SELECT * FROM Materials WHERE (Material=@Material)

SELECT * FROM Materials WHERE (Type=@Type)

SELECT * FROM Materials

If you come up with a fancy query fomulation to pack all these into one
query you will prevent SQL Server from finding the best plan for each one.
Instead every search will be maximally expensive.

David
 
Back
Top