Add something like:

IF(:Variable is not null) then
suspend;



Robert martin wrote:

>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.
>
>
>