Hi Guys,

The following Code gets two dates and sets the firstmonth date to the 16 of the month and the lastmonths date to the 15 of the month. Then it passes these dates into another procedure. That is where i think im getting the error (EXECUTE IMMEDIATE). Thanks for any help!


--THE CODE--
CREATE PROCEDURE PROCEDUREDATE1()
SPECIFIC PROCEDUREDATE1

LANGUAGE SQL

begin

declare SQLSTMT Varchar(1000);
declare BeginDate date;
declare EndDate date;

declare test int;
declare case1 date;
declare case2 date;
declare case3 date;
declare case4 date;


select LastMonth -16 days into case1 from BusinessDate where date_type='CQTR';
select LastMonth -15 days into case2 from BusinessDate where date_type='CQTR';
select LastMonth -14 days into case3 from BusinessDate where date_type='CQTR';
select LastMonth -13 days into case4 from BusinessDate where date_type='CQTR';

select day(LastMonth)into test from BusinessDate where date_type='CQTR';


SELECT CASE
when test=31 THEN case1
when test=30 THEN case2
when test=29 THEN case3
else case4
end case
into EndDate
FROM SYSIBM.SYSDUMMY1;


select FirstMonth +15 days into BeginDate from BusinessDate where date_type ='CQTR';


SET SQLSTMT = 'call ProcedureDate2(?,?,?,BeginDate, EndDate,.97)';
execute immediate SQLSTMT;
end



--THE ERROR--

Run ProcedureDate1 ()

PROCEDUREDATE1- Run started.
Data returned in result sets is limited to the first 50 rows.
Data returned in result set columns is limited to the first 100 bytes or characters.
PROCEDUREDATE1- Calling the stored procedure.
PROCEDUREDATE1- Exception occurred while running:
A database manager error occurred.SQLCODE: -206, SQLSTATE: 42703 - "BeginDate" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=3.53.71
PROCEDUREDATE1- Roll back completed successfully.
PROCEDUREDATE1- Run failed.

1) Try something like,
SET SQLSTMT = 'call ProcedureDate2(?,?,?,''' || BeginDate || ''', ''' || EndDate || ''',.97)';

2) And, you didn't gave the values for parameter markers(i.e. "?") when execute immediate.

3) One of my question is why didn't you call ProcedureDate2 directly than execute immediate?

Hi Tonkuma,

For ProcedureDate2(?,?,?,
The 1st "?" is an out parameter type char(5)
The 2nd "?" is an out parameter of type Int
The 3rd "?" is an out parameter of type varchar(100)

So what am i suppose to put instead of the "?" when i execute immediate

And for your question i just tried and got an error:

The parameter mode (in, out, inout) is not valid for parameter in procedure ProcedureDate2

Thanks.

Declare variables to receive the output from the procedure.
How did you think to get the output without something that?

Check the 4th to 6th parameter mode.