Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    36

    Unanswered: Dynamic SQl generation and Screen output using SQL scripting

    I want to generate a sql query in my stored proc as follows:

    Select * from ( select carrierid,planid,effectivedate,row_number() over() as RN from Tbl_group) as yy where RN between 10 and 50

    But I have to generate it dynamically.
    So in my stored proc I am using scripting like this

    CREATE PROCEDURE SP_TEST(IN P_SELECT_FIELDS VARCHAR(50), IN begin1 varchar(5), IN end1 varchar(5), IN CreateTable char(1) )
    -- some code
    DECLARE V_SQL VARCHAR(100);
    --some code

    SET V_SQL = 'SELECT * from ( select ' || P_SELECT_FIELDS || ' ,row_number() over() as RN from Tbl_group) as yy where RN between ' || begin1 || ' and ' || end1 ;

    PREPARE V_DYNAMIC FROM V_SQL;
    --some code

    The procedure gets compiled but when I call this, it gives following error,

    SQL0104N An unexpected token "w" was found following "rom
    testgroup) as yy". Expected tokens may include:
    "END-OF-STATEMENT

    -- calling the proc from command center ->
    call DYNAPROCTEST(' carrierdrid, planid, id ', '20','30','N')@

    SQL0104N An unexpected token "w" was found following "rom
    testgroup) as yy". Expected tokens may include:
    "END-OF-STATEMENT

    Can you tell me what's wrong with V_SQL

    Also let me know how to generate Screen output using SQL scripting like

    !echo generated SQl is ...@ this works
    but I want something like
    !echo generated SQl is || V_SQL ...@ it's not compiling

    what's the correct syntax??
    Urgently need your help

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    The value in V_SQL is being truncated. Try varchar(200).

    I don't know about generated SQL output in SPs. I don't think my DB2 version supports it (7.2.4)

  3. #3
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    36

    Dedug Procedure using Echo to Screen

    Thanks a lot Damian. Problem is solved.

    But It will be real helpful if the Echo thing is solved. It is required to Debug procedures.

    !echo generated SQl is ...@ --this works
    but I want something like
    !echo generated SQl is || V_SQL ...@ it's not compiling in Db2 8.1
    --V_SQL is a variable
    Can anybody tell me the correct syntax,please

  4. #4
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Does...

    !echo V_SQL

    ... output the literal 'V_SQL' or does it evaluate to the value of V_SQL?

    If it's the former, I'd guess that you can't do this. If it's the latter, try concatenating your text and the value of V_SQL into another variable and outputting that.

    i.e.

    V_NEW_VAR = 'generated SQl is' || V_SQL

    !echo V_NEW_VAR

    But like I said, I'm not familiar with generated output.

    The SP builder is pretty good for debugging SP's.

  5. #5
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    36
    Thanks for your suggestions. However right now I am mainly looking for console based debugging, so SP builder may not be used at this moment. Anyway thank you once again for all the help you have provided.

Posting Permissions

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