community.borland.com

Article #25178: Input / Output parameters for Stored Procedures

Problem:
'Column unknown' errors when running stored procedure

Solution:
(example broken)
-- snip --
ALTER PROCEDURE CALC_UMSATZ_2 (KUNDENID INTEGER,
 JAHR CHAR(10))
RETURNS (UMSATZLFD NUMERIC(10,2))
AS
BEGIN
	/* Umsatz lfd. jahr */
	SELECT BP.POSSUMME
	FROM BESTELLUNGEN B, BESTELLPOS BP
	WHERE (B.KUNDENID = :kundenid) AND
	(BP.BESTELLID = B.BESTELLID) AND
	(CAST(B.BESTELLDATUM AS DATE) > CAST(JAHR AS DATE))
	INTO    :umsatzlfd;
	SUSPEND;
	END

Resolution:  input/output parameters must be references via a preceding ":"

(example fixed)
-- snip --
ALTER PROCEDURE CALC_UMSATZ_2 (KUNDENID INTEGER, 
JAHR CHAR(10))
RETURNS (UMSATZLFD NUMERIC(10,2))
AS
BEGIN
	/* Umsatz lfd. jahr */
	SELECT BP.POSSUMME
	FROM BESTELLUNGEN B, BESTELLPOS BP
	WHERE (B.KUNDENID = :kundenid) AND
	(BP.BESTELLID = B.BESTELLID) AND
	(CAST(B.BESTELLDATUM AS DATE) > CAST(:JAHR AS DATE))
	INTO    :umsatzlfd;
	SUSPEND;
	END

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

(broken portion of code)

(CAST(B.BESTELLDATUM AS DATE) > CAST(JAHR AS DATE)) 
(CAST(B.BESTELLDATUM AS DATE) > CAST(:JAHR AS DATE)) 

Last Modified: 29-SEP-00