How to decare a variable in Teradata sql and call them in a select statement?

Question

I want to declare two variable and use thoes variable in where condition of a select statement in teradata, but I am getting an error in that.

Create Procedure Demo()

Begin
Declare REnd Date;
Declare RStart Date;

Set REnd = (select max(CalendarDate) as REnd 
            from Table1 
            where CalendarDate = MonthEndDate
            and  Monthofyear in ('June', 'December')
            and CalendarDate < Current_date()
           );

Set RStart = (select max(CalendarDate) as RStart
            from Table1 
            where CalendarDate = (CalendarDate - Extract(Day From CalendarDate)+1)
            and  Monthofyear in ('January', 'July')
            and CalendarDate < REnd
           );

Call dbc.sysexecsql(('select * from table 2 where reviewdate between' ||REnd|| 'and' ||RStart|| ');');
End;

Answer 1

No Dynamic SQL needed.

REPLACE PROCEDURE Demo()
DYNAMIC RESULT SETS 1 -- SP will return a result set
BEGIN 
   Declare REnd Date;
   Declare RStart Date;

   Set REnd = (select max(CalendarDate) as REnd 
               from Table1 
               where CalendarDate = MonthEndDate
               and  Monthofyear in ('June', 'December')
               and CalendarDate < Current_date()
              );

   Set RStart = (select max(CalendarDate) as RStart
               from Table1 
               where CalendarDate = (CalendarDate - Extract(Day From CalendarDate)+1)
               and  Monthofyear in ('January', 'July')
               and CalendarDate < REnd
              );

   -- Return result set
   BEGIN
      DECLARE cur2 CURSOR WITH RETURN ONLY FOR
       -- this is your query using parameters
         select * from table2 
         where reviewdate between :RStart and :REnd;

      OPEN cur2; -- don't close, otherwise no result set
   END;
END;

Cursors are evil for processing data, but this is not a real cursor, it's just the lengthy Standard SQL syntax for returning a result set:-)