I struggling with my SP to avoid returning null to client, but it
doesn't work.I have given my SP here. I am trying to retrieve items
that have discount, if no such item found then I want my SP to
return zeros instead of nulls.

Refer the SP Line "IF(:DISCOUNT IS NULL OR :SCHEME IS NULL)THEN"

Any help appreciated

Regards,

Muthu Annamalai

---------------------SP---------------------------------------


CREATE PROCEDURE GETSALESDISCOUNT(
BARCODE VARCHAR(13))
RETURNS (
DISCOUNT DECIMAL(18,2),
SCHEME VARCHAR(50),
BUY SMALLINT,
FREE SMALLINT,
OFF DECIMAL(18,2),
ORMORE SMALLINT)
AS
BEGIN
FOR
SELECT ITEMDISCOUNT.DISCOUNT,ITEMDISCOUNT.SCHEME
FROM ITEMDISCOUNT,ITEMSTYLE
WHERE ITEMSTYLE.BARCODE =:BARCODE AND
ITEMSTYLE.ID = ITEMDISCOUNT.ITEMSTYLEID AND
ITEMDISCOUNT.STARTDATE <= 'NOW' AND
ITEMDISCOUNT.ENDDATE >= 'NOW'
INTO :DISCOUNT,:SCHEME
DO
BEGIN
IF (:DISCOUNT IS NULL OR :SCHEME IS NULL) THEN
BEGIN
DISCOUNT =0;SCHEME ='';BUY =0; FREE =0;OFF =0.0;
ORMORE =0;
SUSPEND;
EXIT;
END
IF (:SCHEME !='') THEN
BEGIN
SELECT BUY, FREE, OFF, ORMORE
FROM DISCOUNTVALUECODE
WHERE DISCOUNTVALUECODE.SCHEME =:SCHEME
INTO :BUY,:FREE,:OFF,:ORMORE;
END
ELSE
BEGIN
BUY =0; FREE =0; OFF =0; ORMORE =0;
END
SUSPEND;
END

END