Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011
    Posts
    2

    Unanswered: Execute Immediate

    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.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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?

  3. #3
    Join Date
    Aug 2011
    Posts
    2
    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.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    Declare variables to receive the output from the procedure.
    How did you think to get the output without something that?

    The parameter mode (in, out, inout) is not valid for parameter in procedure ProcedureDate2
    Check the 4th to 6th parameter mode.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •