Topic
2 replies Latest Post - ‏2014-01-05T09:29:12Z by G.Paulus
tinu
tinu
1 Post
ACCEPTED ANSWER

Pinned topic Dynamic db2 query in stored procedure

‏2013-12-30T07:33:41Z |

I am new to Db2 . Need a help to create a procedure with dynamic query

--------------------------------------------------------------------------------

CREATE PROCEDURE USP_TEST(IN APPLID CHAR(12))
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE getSEG_CODE CHAR(3);--
DECLARE cursor1 CURSOR FOR s1;--
set getSEG_CODE="SELECT SEG_CODE FROM DEPT_MASTER where APPL_ID='"||APPLID||"'";--
PREPARE cursor1 from getSEG_CODE;--
OPEN cursor1;--
END

-------------------------------------------------

Following is the error message coming

SQL Error [42703]: "SELECT SEG_CODE FROM EXIMTRX.DEPT_MASTER where APPL_ID='" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.16.53
  "SELECT SEG_CODE FROM DEPT_MASTER where APPL_ID='" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.16.53
 

Also , how to get   information printed  when executing the procedure(like dbms_output in orcle)

Thanks in advance

 

Regards

 

Updated on 2013-12-30T07:34:23Z at 2013-12-30T07:34:23Z by tinu
  • Zahni
    Zahni
    15 Posts
    ACCEPTED ANSWER

    Re: Dynamic db2 query in stored procedure

    ‏2014-01-03T14:17:14Z  in response to tinu

    Is "EXIMTRX" the correct Schema ?

     

  • G.Paulus
    G.Paulus
    9 Posts
    ACCEPTED ANSWER

    Re: Dynamic db2 query in stored procedure

    ‏2014-01-05T09:29:12Z  in response to tinu

    Hello tinu,

     

    First of all getSEG_CODE is declared as CHAR(3) but a value greater than CHAR(3) is assigned. So this will not work.

    DECLARE getSEG_CODE CHAR(3);--

    ...

    set getSEG_CODE="SELECT SEG_CODE FROM DEPT_MASTER where APPL_ID='"||APPLID||"'";--

     

    The prepare statement is wrong. The Cursor is declared as follows: DECLARE cursor1 FOR s1, so you don't have to prepare cursor1 but you have to prepare s1 from getSEG_CODE.

    DECLARE cursor1 CURSOR FOR s1;--

    SET segSEG_CODE = ..... ;--

    PREPARE s1 FROM getSEG_CODE;--

     

    Best regards,

    Gerhard Paulus

    Updated on 2014-01-05T09:43:41Z at 2014-01-05T09:43:41Z by G.Paulus