At 09:54 PM 2/05/2005 +1000, you wrote:

>Hi to all,
>
>Using FB 1.5 and Delphi 5.
>
>I need to create a custom search string in a stored procedure subject to
>the fields that the user has selected.
>
>To explain more,
>
>On a particular form in our application we have 10 user input devices,
>some are standard edit boxes, some are drop down lists.
>
>I need the "where" clause of the stored procedure to cope with this set
>up subject to what ever the user has selected.
>
>If I was creating a normal sql query statement then I could just add
>together the required bits and end up with the final "where" clause.
>
>eg -
>ABC.SQL.Add('where Detect= :123);
>if X = true then ABC.SQL.Add('and Text1= :T1);
>if C = true then ABC.SQL.Add('and Text2= :T2);
>
>and so on and so on until I get the final "where" clause for the query.
>
>My question is how do I set up a stored procedure that could be created
>at runtime ?

You don't create stored procedures at runtime.

>Is this do able or can I only do this with "querys" and then send the
>whole query to the DB server.

It really seems that you don't know about parameters in dynamic
SQL. Please signal "yes" or "no" to this. :-)

e.g. (for Delphians):

Statement (which can be persistent and should be, if possible for this type
of application, since you can prepare it once and keep it prepared for a
series of "hits" on the search)::::

select blah from aTable
where Detect = :Detect
and Text1 = :Text1
and Text2 like :Text2

....in BeforeOpen:

with abc do
begin
ParamByName('Detect') := YourControl.Text;
ParamByName('Text1') := AnotherControl.Text;
ParamByName('Text2) := YetAnother.Text + '%';
end;

Beyond that, please understand that this isn't a Delphi list. The odd
question here and there won't hurt, but frequent or lengthy threads on
Delphi programming technique are off-topic. The recommended Delphi access
components have their own support lists. If you're struggling on with the
standard, out-of-the-box Borland stuff, then try this one:

<mailto:delphi-db-request@...?subject=subscribe>

^heLen