I have two stored procedures and one calls the other. If
the caller passes a null for one of the parameters (the fourth) a delete
query in the called function will not work.
If I pass a null as the 4th parameter (_seqSalesOrderLine) the delete
query below will not match any records, and my business rule fails. If I
temporarily (for testing only) change the asterisked line to read
and seqSalesOrderLine = null
the query works as I would expect.
Am I missing something (please tell me) or is this a bug when passing in a
null value?
Thanks for any help,
Mike.
Bits of called function (asterisks mark important lines)
===========================================================================
create function salesOrderTax_apply(int8,int8,
text,int8,float8,
int8,text) returns integer as '
declare
...
* _seqSalesOrderLine alias for $4;
...
begin
...
delete from salesOrderTax
where seqSalesOrder = soRec.seqSalesOrder
and codeSalesOrderTax = _codeSalesOrderTax
* and seqSalesOrderLine = _seqSalesOrderLine
;
...
return null; --bogus return value
end;
' language 'plpgsql';
Bits of calling function (asterisks mark important lines)
===========================================================================
create function salesOrderControl_freightTax(int8, int8,text)
returns integer as '
declare
...
_seqTaxSchedule int8;
...
begin
...
-- get the appropriate tax schedule
select seqTaxSchedule
into _seqTaxSchedule
from carrier
where name = soRec.carrier_name
;
...
perform
salesOrderTax_apply(soRec.seqSalesOrder, _seqTaxSchedule,
* ''Freight'', null, socRec.totalFreight,
_txSeq, _txUser
);
return null; --bogus return value
end;
' language 'plpgsql';
=========================================
Mike Finn
mike.finn@tacticalExecutive.com
