I'm writing a stored procedure to move the data from the DBQL tables to another table for storage.
I'm using three variable to allow me to separate the logs by year.
This is the first Stored Procedure I've ever written and I am fairly new to Teradata as well.
Here is the definition of the SP:
CREATE PROCEDURE mydatabase.Proc1
(
arc_year char(4)
,tstamp1 TIMESTAMP
,tstamp2 TIMESTAMP
)
BEGIN
DECLARE table_year CHAR(4);
DECLARE stamp1 CHAR(19);
DECLARE stamp2 CHAR(19);
SET table_year = arc_year;
SET stamp1 = tstamp1;
SET stamp2 = tstamp2;
call dbc.sysexecsql('INSERT INTO logarchive.TB_DBQLObjTbl_' || table_year ||
' SELECT * FROM DBC.DBQLObjTbl WHERE CollectTimeStamp BETWEEN TIMESTAMP ' || stamp1 || ' AND TIMESTAMP ' || stamp2 || ';');
END;
It seems pretty straight forward to me, but when I execute the SP within SQL Assistant it blows off with a 3707.
Here's my execution call.
call mydatabase.Proc1 ('2007','2007-01-01 00:00:00','2007-12-31 11:59:59')
Are Timestamps not allowed as host variables?
Any help would be greatly appreciated.
Thanks,
Tim Schmolzi
Missouri Office of Administration
ITSD-DOR Database