All Forums Database
RGlass 35 posts Joined 09/04
06 Dec 2006
Stored Procedure Problem

Hello all,I'm trying to create a stored procedure that will 1. Create a Table2. Populate the table from sys_calendar.calendar base on input parm.3. Move that to output parm.4. Drop the tableQuestion: Can I do this in one procedure, using dbc.sysexecsqlI'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); ENDCAll otdt('dbnm', 'tbnm', '2004-01-01')

Fred 1096 posts Joined 08/04
06 Dec 2006

Think about the value of the string expression. This is NOT what you want:'SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE < '||VAR_DATEbecause 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))||''''

RGlass 35 posts Joined 09/04
07 Dec 2006

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

You must sign in to leave a comment.