I was under the impression that any SP that finds no results to return will
return a NULL row. Is that not the case?

Myles

===========================
Myles Wakeham
Director of Engineering
Tech Solutions Inc.
Scottsdale, Arizona USA
Phone (480) 451-7440
Web: www.techsol.org

> -----Original Message-----
> From: Robert martin [mailto:rob@...]
> Sent: Thursday, March 03, 2005 3:39 PM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] More novice stored procedure questions
>
>
> Hi All
>
> Some may remember my SP that I had problems with. I now have it running
> in the following format.
>
>
> CREATE PROCEDURE pr_BackOrderedItems (DrTransRef Numeric(11))
> RETURNS (
> TransNum Numeric(11),
> ClOrderRef Numeric(11),
> OrderDate Date,
> CliOrderNo VarChar(16),
> ClientCode VarChar(20),
> ClientName VarChar(40),
> SellPrice Numeric(13,2),
> ItemCode VarChar(20),
> MakeName VarChar(30),
> Price Numeric(13,2),
> Quantity Numeric(15,4),
> Descriptn VarChar(254),
> Avail Integer,
> Due Date)
> AS
> DECLARE VARIABLE i_ClOrderRef Numeric(11);
> DECLARE VARIABLE i_TransNum Numeric(11);
> DECLARE VARIABLE i_ItemRef Numeric(11);
> BEGIN
> FOR
> SELECT DISTINCT cs.ClOrderRef, dt.TransNum
> FROM DebtorInvoiceLine d
> JOIN DebtorTrans dt ON dt.DrTransRef = d.DrTransRef
> JOIN ClientStock cs ON cs.CliStkRef = d.CliStkRef
> WHERE d.DrTransRef = :DrTransRef
> AND d.Subrank = 999
> AND ClOrderRef IS NOT NULL
> INTO i_ClOrderRef, i_TransNum
> DO
> BEGIN
> SELECT :i_TransNum, co.ClOrderRef, cs2.ItemRef,
> co.OrderDate, co.CliOrderNo, c.ClientCode, ClientName, s.SellPrice,
> s.ItemCode, m.Descriptn, cs2. Sellprice, cs2.Quantity, cs2.Descriptn
> FROM ClientStock cs2
> JOIN ClientOrders co ON co.ClOrderRef =cs2.ClOrderRef
> JOIN Client c ON c.EntityRef = co.EntityRef
> JOIN Stock s ON s.ItemRef = cs2.ItemRef
> JOIN Item i ON i.ItemRef = cs2.ItemRef
> LEFT JOIN Make m ON m.MakeRef = i.MakeRef
> WHERE cs2.ClOrderRef = :i_ClOrderRef
> AND cs2.Subrank = 999
> AND cs2.PSlipRef IS NULL
> AND cs2.TransRef IS NULL
> AND (cs2.ActionType IS NULL OR cs2.ActionType = '')
> INTO :TransNum, :ClOrderRef, :i_ItemRef, :OrderDate,
> :CliOrderNo, :ClientCode, :ClientName, :SellPrice, :ItemCode, :MakeName,
> :Price, :Quantity, :Descriptn;
>
> SELECT sum(sl.shelfqty), min(spl.PREARRDATE)
> FROM StockLevel sl
> LEFT JOIN SupplyLine Spl on sl.itemref = spl.itemref
> WHERE sl.Itemref = :i_ItemRef
> INTO :Avail, :Due;
>
>
> SUSPEND;
> END
> END
>
>
> The first part builds a list of orders referenced in this invoice. The
> second part lists any unsupplied items in those orders. My question is,
> how do I return no results when no outstanding items exist? Currently I
> return a Null record.
>
> --
> Rob Martin
> Software Engineer
>
> phone +64 03 377 0495
> fax +64 03 377 0496
> web www.chreos.com
>
> Wild Software Ltd
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>