Need help on writing teradata stored procedure

Database

Need help on writing teradata stored procedure

Hello All,

I have one simple requirement on writing stored procedure.Requirement is as follows.


create stored procedure xxxx(In load_type varchar(20),load_date date)

declare vload_type varchar(20);

declare vload_date date;

begin

IF load_type='HISTORY' THEN

CREATE TABLE VT_TEMP

(MEETING_ID BIGINT,

MEETING_START_DATE date,

MEETING_END_DATE date)

(SELECT MEETING_ID,MEETING_START_DATE,MEETING_END_DATE from meetings)

ON COMMIT PRESERVE ROWS;

ELSEIF

CREATE TABLE VT_TEMP

(MEETING_ID BIGINT,

MEETING_START_DATE date,

MEETING_END_DATE date)

(SELECT MEETING_ID,MEETING_START_DATE,MEETING_END_DATE from meetings

where meeting_date between current_date-60 and current_date.)

ON COMMIT PRESERVE ROWS;

END IF;

END;

My requirement is,i dont want to use two separte insert statements,have only one CREATE block and make the select dynamically absed on LOAD_TYPE.If current then only WHERE caluse come into picture.

How can i do that ?

I tried assigning the WHERE caluse to one varibale and using that ,but no luck.

SET MYQUERY='where meeting_date between current_date-60 and current_date';

1 REPLY
Teradata Employee

Re: Need help on writing teradata stored procedure

Put the entire SQL statement in a variable, and dynamically execute it:

SET MYQUERY='CREATE TABLE ...'||CASE WHEN LOAD_TYPE='HISTORY' THEN ' WHERE...' ELSE '' END||' ON COMMIT PRESERVE ROWS';

EXECUTE IMMEDIATE MYQUERY;