Stored Procedure Problem

Database
Enthusiast

Stored Procedure Problem

Hello all,
I'm trying to create a stored procedure that will
1. Create a Table
2. Populate the table from sys_calendar.calendar base on input parm.
3. Move that to output parm.
4. Drop the table

Question: Can I do this in one procedure, using dbc.sysexecsql
I'm at step 2 and when I try to reference var_date( in the same way I referenced dbnm, tbnm, I get no records, If I use just Date(in place of ||Var_date when I create Procedure) as parm I Insert correct records from sys_cal.calendar when I call it;

Any help would be greatly appreciated.

REPLACE PROCEDURE dbname.outdt
(IN DBNM CHAR(8), IN TBNM CHAR(10), IN VAR_DATE DATE OUT ODATE DATE)

BEGIN

CALL DBC.SYSEXECSQL
('CREATE TABLE ' || DBNM || '.' || TBNM ||
' (G_DT DATE FORMAT ''YYYY-MM-DD''
,G_DAY BYTEINT)');

CALL DBC.SYSEXECSQL
('INSERT INTO ' || DBNM ||'.'|| TBNM ||
' SEL CALENDAR_DATE, DAY_OF_MONTH ' ||
'FROM ' ||
'SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE < '||VAR_DATE);

END

CAll otdt('dbnm', 'tbnm', '2004-01-01')
2 REPLIES
Teradata Employee

Re: Stored Procedure Problem

Think about the value of the string expression. This is NOT what you want:
'SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE < '||VAR_DATE
because that would become (for example)
'SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE < 2006-12-06'
so the term on the right is the integer expression 2006 minus 12 minus 6 or 1988 which in turn would correspond to an invalid date (1900-19-88) that falls between 1900-12-31 and 1901-01-01.

If you could use parameter substitution in dynamic SQL, you would say (as I suspect you did with DATE)
'SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE < VAR_DATE'

An alternative is to explicitly build a date literal string, e.g.
'SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE < date'''
||cast(cast(VAR_DATE AS FORMAT('YYYY-MM-DD') AS CHAR(10))||''''
Enthusiast

Re: Stored Procedure Problem

The alternative of casting to a char worked for me.
Thanks,