I have a query that is used for finding a value in a number of columns of a
row:

IntegerColumn
StringColumn1
StringColumn2

SELECT * FROM TABLE WHERE
IntegerColumn=:SearchValue OR
StringColumn1=:SearchValue OR
StringColumn2=:SearchValue

Do I need to define multiple parameters, as seems to automatically occur
when I enter this SQL, or is there a way I can just have one parameter for
all three locations I need to insert the search value (also considering
different types).

I had previously been creating the query manually at runtime, but now I am
trying to use parameterized queries more and ran into this obstacle.

Thanks,
-Johnnie

Johnnie,

<< Do I need to define multiple parameters, as seems to automatically occur
when I enter this SQL, or is there a way I can just have one parameter for
all three locations I need to insert the search value (also considering
different types). >>

You need to define multiple parameters and refer to them by their index
position and not by their name.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in
news:9590CCDB-3CC7-47CD-AD37-25002C703E8F@news.elevatesoft.com:

> You need to define multiple parameters and refer to them by their
> index position and not by their name.
>

Tim,

 As I understand it he needs to find any row with a given numeric value in  
any one (or more) of several fields that may be numeric or string.

 Couldn't he define just one parameter for each type and use them by name?

SELECT * FROM TABLE WHERE
IntegerColumn=:IntSearchValue OR
StringColumn1=:StrSearchValue OR
StringColumn2=:StrSearchValue

Or perhaps I'm not properly tuned in today.

--
Chris

"Chris Erdal" <chris@No-Spam-erdal.net> wrote in message
news:Xns97FDA66CDB66C14torcatis@64.65.248.118...
> "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in
> news:9590CCDB-3CC7-47CD-AD37-25002C703E8F@news.elevatesoft.com:
>
>> You need to define multiple parameters and refer to them by their
>> index position and not by their name.
>>
>
>  As I understand it he needs to find any row with a given numeric value in
> any one (or more) of several fields that may be numeric or string.
>
>  Couldn't he define just one parameter for each type and use them by name?
>
> SELECT * FROM TABLE WHERE
> IntegerColumn=:IntSearchValue OR
> StringColumn1=:StrSearchValue OR
> StringColumn2=:StrSearchValue

This creates three parameters when entered into the SQL editor. It would
seem logical that it create two since one is a duplicate.

Maybe a feature request?

-Johnnie

Johnnie,

<< This creates three parameters when entered into the SQL editor. It would
seem logical that it create two since one is a duplicate. >>

Parameters work on the basis of each constant *instance*, not by name,
unless you use ParamByName().

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:19193943-462A-4E0D-9F63-656983FE326E@news.elevatesoft.com...
> Johnnie,
>
> << This creates three parameters when entered into the SQL editor. It
> would seem logical that it create two since one is a duplicate. >>
>
> Parameters work on the basis of each constant *instance*, not by name,
> unless you use ParamByName().

I do use ParamByName() exclusively, so does that mean it *will* populate
both duplicated parameter names?

This is an instance where it would be nice to see the resultant SQL for
testing parameters.

-Johnnie

Johnnie,

<< I do use ParamByName() exclusively, so does that mean it *will* populate
both duplicated parameter names? >>

Yes.

<< This is an instance where it would be nice to see the resultant SQL for
testing parameters. >>

I agree.  However, since we don't have that yet, the simple rule is:

1) By name - all like-named parameters are assigned the same value
2) By index - each parameter is assigned a unique value unless you assign
the same value for each

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:37D3DAC3-B58F-44F8-9F6E-1B058F885A61@news.elevatesoft.com...
> << I do use ParamByName() exclusively, so does that mean it *will*
> populate both duplicated parameter names? >>
> Yes.

Got it. I just thought there might e an issue because it appeared as two
parameters at design time.

>
> << This is an instance where it would be nice to see the resultant SQL for
> testing parameters. >>
> I agree.  However, since we don't have that yet, the simple rule is:
>
> 1) By name - all like-named parameters are assigned the same value
> 2) By index - each parameter is assigned a unique value unless you assign
> the same value for each

Got it. Just agreeing with a previous poster that this information would be
useful.

Thanks a bunch.