Hi Peter,

>>> What kind of things were you planning on doing with SP's?

>>I don't know as yet. I am VERY new to FB. I have done most of my work in
>>Delphi and a database called Flashfiler (a record based C/S DB).

We use Delphi too - except we used to use paradox. I could NEVER go back now!

>>I have a lot to learn about FB

Do yourself a favour and get Helen's book (The Firebird Book) if you haven't already. It will save you many hours of your time, not to mention headaches.

>>and quite a few changes to make to my app
>>to convert it to FB. I did attempt a change and found out more and more
>>that needed to be adjusted/modified/converted. As I am changing the Delphi
>>code at the moment I have decided now to complete this and ensure it
>>performs as expected before converting to FB.

I would be interested to know what Delphi components you decided to use for accessing FB.

>>I would appreciate any sp's you think may be of use to me. I am willing to
>>read and learn from them. I anticipate they would help me understand the
>>capabilities of and use of sp's.

SP's can be used for many things. Only your imagination is your limit ;-)
I started off using selectable SP's much as one would use a view, and then just extended things from there.

Firstly, selectable SP's have a few extra things that views don't, namely:
Input Parameters (much like Delphi procedures do)
Variables
and most importantly, can execute more than one SQL statement (just like a Delphi procedure can execute more than one statement.)

These statements also don't need to be select, or update statements. They can be for loops, and things like i = i + 1;
(translated to Pascal/Delphi: i := i + 1;)
So, stored procs are very similar to programming in code.

With Paradox (and Flashfiler) a database was purely a place to store data - it was quite a mind-bender to start actually executing 'code'!

So as you can see, asking "what can you do with stored procedures?" is a bit like asking "what can you do with C++?" - OK, maybe not quite as powerful as C or Delphi, etc, but you get the picture?

I use stored procedures as a base for all my reports.
Totals are not stored, but calculated as needed (if you get your indices right, then speed is not a problem) No need for 'rebuilds' to correct total field that have gone out of synche.

If you are going to write SP's then, I recommend you use a tool such as IBExpert to do this. Yes, it is possible to write them in Notepad, but try IBExpert (Personal Ed is free), and you'll see what I'm talking about. There is code-completion, sysntax highlighting, compiling, etc - all of those niceties that us Delphi programmers are used to.

Below is a sample procedure:
(This proc - I believe, but may very well be wrong - will calculate the true FIFO cost of a stock item calculated from the stock transactions.
I think I may have been trying to get something else out, and then discovered that I may have FIFO, so just renamed it, and carried on working with the original as something else)

It's a nice eg. though, as it shows multiple statements, and use of the "suspend;" statement in interesting places. (BTW, calling suspend, will output a row to the dataset returned by the SP)

It also calls another SP, namely CALC_STOCKWAREHOUSE_LEVELS.

I probably shouldn't be calling exit's (like you shouldn't in delphi either...)

------------------------------------------------------------------

CREATE PROCEDURE CALC_STOCK_FIFO_COST (
ISTOCKCODE VARCHAR(30) CHARACTER SET NONE,
IWAREHOUSECODE VARCHAR(4) CHARACTER SET NONE,
ITODATE TIMESTAMP)
RETURNS (
OLEVEL DECIMAL(18,5),
OEXTCOST DECIMAL(18,5))
AS
DECLARE VARIABLE LLEVEL DECIMAL(18,5);
DECLARE VARIABLE LCOSTPRICE DECIMAL(18,5);
DECLARE VARIABLE LCOSTPRICEPER DECIMAL(18,5);
DECLARE VARIABLE LQTY DECIMAL(18,5);
begin
select OQuantity1
from CALC_STOCKWAREHOUSE_LEVELS(:IStockCode,:IWarehouseCode, :IToDate)
into OLevel;

OExtCost = 0.0;

if (OLevel <= 0.0) then
begin
suspend;
exit;
end

LLevel = 0.0;

for
select
ST.Qty1,
ST.CostPrice,
ST.CostPer
from STK_STOCKTRANSACTION ST
where (ST.StockCode = :IStockCode)
and ((ST.WarehouseCode = :IWarehouseCode) or (:IWarehouseCode is null))
and ((ST.TranDteTme <= :IToDate) or (:IToDate is null))
order by ST.TranDteTme desc, ST.ID desc

into
LQty,
LCostPrice,
LCostPricePer

do
begin
LLevel = LLevel + LQty;

if (LQty > 0.0) then
OExtCost = OExtCost + (LQty * (LCostPrice / LCostPricePer));

if (LLevel >= OLevel) then
begin
OExtCost = OExtCost - ((OLevel - LLevel) * (OExtCost / LLevel));
suspend;
exit;
end
end
end

------------------------------------------------------------------
OK, OK, that one might scare you away for life:
Here is the simplest one I could find:

(This is probably the kind of thing you would start off with)

------------------------------------------------------------------
CREATE PROCEDURE LIST_UOM_USED
RETURNS (
OUOM VARCHAR(5) CHARACTER SET NONE)
AS
begin
for
select
cast(CS.DefaultUOM as varchar(5))
from SYS_CompanySetup CS

union

select
cast(S.UOM as varchar(5))
from STK_StockItem S


into
OUOM
do
begin
if ((OUOM is not null) and (OUOM <> '')) then
suspend;
end
end
------------------------------------------------------------------

NB. You'll probably be able to convert you app without touching SP's (as you never had them before) but as you develop, and enhance stuff, you'll find more and more use for them.

Hope this helps.

Maya