Stored Proc - why can't I use a parameter or variable here ?

General
Enthusiast

Stored Proc - why can't I use a parameter or variable here ?

IF ( ACTIVITY_COUNT < :pvRowsNeeded ) THEN
    SIGNAL SQLSTATE 'U0001' SET MESSAGE_TEXT='SP_ONBOARD_JOURNEY_TTS - Failed to INSERT properly, not enough rows selected';
END IF;

pvRowsNeeded is an integer parameter passed to the stored proc.

When I replace the parm with "5000", the procedure will compile.

 


Accepted Solutions
Enthusiast

Re: Stored Proc - why can't I use a parameter or variable here ?

Thanks for that....

but I had other variable references in other places in the proc and the compiler accepted them !!

1 ACCEPTED SOLUTION
7 REPLIES
Highlighted
Senior Apprentice

Re: Stored Proc - why can't I use a parameter or variable here ?

Simply remove the colon in front of the parameter, it's an SP, not a macro :-)

 

IF ( ACTIVITY_COUNT < pvRowsNeeded ) THEN
    SIGNAL SQLSTATE 'U0001' SET MESSAGE_TEXT='SP_ONBOARD_JOURNEY_TTS - Failed to INSERT properly, not enough rows selected';
END IF;
Enthusiast

Re: Stored Proc - why can't I use a parameter or variable here ?

Thanks for that....

but I had other variable references in other places in the proc and the compiler accepted them !!

Re: Stored Proc - why can't I use a parameter or variable here ?

Can you please paste the code that has no issue while compiling.

Enthusiast

Re: Stored Proc - why can't I use a parameter or variable here ?

REPLACE PROCEDURE NDW_EBI_WORK.SP_ONBOARD_JOURNEY_CALLS ( IN pvRowsNeeded INTEGER,
                                                                                                                       IN pvDaysBack INTEGER
																													   )
BEGIN
    DECLARE vCNT SMALLINT;
    DECLARE vSQL_TEXT VARCHAR(10000);
	
DELETE
FROM   ndw_ebi_work.move_onbrd_conn_calls_wz
WHERE  activity_date >
       (
              SELECT Max(activity_date) - :pvDaysBack - 1
              FROM   ndw_ebi_work.move_onbrd_conn_calls_wz);
Teradata Employee

Re: Stored Proc - why can't I use a parameter or variable here ?

The colon prefix is allowed or even required within DML statements (e.g. DELETE), but not in SQL Control Statements.

Enthusiast

Re: Stored Proc - why can't I use a parameter or variable here ?

Thanks Fred....but my stored proc compiled fine WITHOUT the colon.

Make sure your compiler's parsing routine vets this condition properly and throws an error if a colon is required... or not.

Teradata Employee

Re: Stored Proc - why can't I use a parameter or variable here ?

Not sure I understand your last comment.

For your DELETE statement example:

  • In Teradata mode, using the colon prefix is a best practice, but not required. (There is no column with that name, so the parser assumes you meant to specify the variable.)
  • In ANSI mode, the colon prefix would be required to indicate that it is a reference to a host variable.

For the original IF statement, a colon prefix is not allowed.