Hi Rob
I take your point about not being able to optimize statements - especially
with "starting with, containing..."
However, take a look at this cut-down version of an SP running on an NT4
server - I think its a 500 or 750 mhz machine - 7 users using this stock
system. Responses to a query such as this is typically a second or so -
difficult to measure unless a timer is dropped onto the form and we avoid
that because users get transfixed by it.
This does the same sort of thing as :-

select * from myProc(:aValue) where Lastname Starting With 'A'
but also adds another level of complexity with a location and a type of
transaction

To make this work we pass a query :-
try
with QryStocks do begin
Close;
Sql.Clear;
Sql.Add('Select * From SP_StockLike(:StockSearch,'+
':FromTransType,:UpToTransType,:ThisLocation)');
Sql.Add('ORDER By Stock_No');
Params.ParamValues['StockSearch']:=locSearch+'%';
Params.ParamValues['FromTransType']:=FromTransType;
Params.ParamValues['UpToTransType']:=UpToTransType;
Params.ParamValues['ThisLocation']:=Location;
Open;
if Eof then
MessageDlg('Stock # '+locSearch+' Not On File', mtError, [mbOK],
0);
end;
finally
Screen.Cursor:=crDefault;
end;

This is then translated into something like :-
select * from sp_stocklike('0110%','S','S','H')

COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

/* Stored procedures */

CREATE PROCEDURE SP_STOCKLIKE
(
LOCSEARCH CHAR(15),
FROMTRANSTYPE CHAR(1),
UPTOTRANSTYPE CHAR(1),
THISLOCATION CHAR(1)
)
RETURNS
(
ACNO CHAR(3),
STOCK_NO CHAR(15),
COST_PRICE NUMERIC(15, 2),
RET_PRICE NUMERIC(15, 2),
ACT_PRICE NUMERIC(15, 2),
TRANS_TYPE CHAR(1)
AS
begin
if (:FromTransType='S' and :upToTransType='S') then /* Sold Only */
begin
if (:ThisLocation='Z') then
begin /* All Shops */
for select
Acno, Stock_No, Cost_Price, Ret_Price, Act_Price,Trans_Type
From Stocks
Where Stock_No like :locSearch
And Trans_Type=:FromTransType
And Trans_Type=:UpToTransType
into
:Acno, :Stock_No, :Cost_Price, :Ret_Price, :Act_Price, :Trans_Type
do
begin
suspend;
end
end
else
begin /* Select Shop by Shop Code A-R*/
for select
Acno, Stock_No, Cost_Price, Ret_Price, Act_Price,Trans_Type
From Stocks
Where Stock_No like :locSearch
And Trans_Type=:FromTransType
And Trans_Type=:UpToTransType
And Location=:ThisLocation
into
:Acno, :Stock_No, :Cost_Price, :Ret_Price, :Act_Price, :Trans_Type
do
begin
suspend;
end
end
end
else
begin /*C-Current, R-Returns, Z-Missing, D-Deposit */
if (:ThisLocation='Z') then
begin /* All Shops */
for select
Acno, Stock_No, Cost_Price, Ret_Price, Act_Price,Trans_Type
From Stocks
Where Stock_No like :locSearch
And Trans_Type>=:FromTransType
And Trans_Type<=:UpToTransType
into
:Acno, :Stock_No, :Cost_Price, :Ret_Price, :Act_Price, :Trans_Type
do
begin
suspend;
end
end
else
begin /* Select Shop by Shop Code A-R*/
for select
Acno, Stock_No, Cost_Price, Ret_Price, Act_Price,Trans_Type
From Stocks
Where Stock_No like :locSearch
And Trans_Type>=:FromTransType
And Trans_Type<=:UpToTransType
And Location=:ThisLocation
into
:Acno, :Stock_No, :Cost_Price, :Ret_Price, :Act_Price, :Trans_Type
do
begin
suspend;
end
end
end
end
^

SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;

Reagrds
Alan J Davies