Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Posts
    51

    Execute String as SQL Command

    In SQl Server its possible to concoct a string representing a sql command and then execute it within a stored procedure. This overcomes the problem of receiving 'peices' of a command as a parameter, such as a list of values for an IN phrase, because it allows you to merge them with the string version of the command.

    Does DB2 enable this?

    Thanks,

    Kimball Johnson

  2. #2
    Join Date
    Jan 2003
    Posts
    4,126
    Yes,
    Look at the PREPARE and EXECUTE SQL commands.

    Andy

  3. #3
    Join Date
    Aug 2004
    Posts
    51
    The DB2 help file says this about EXECUTE and EXECUTE IMMEDIATE:

    "This statement can be embedded only in an application program."

    My question was about using such a command within a 'stored procedure'.

    Is the answer still yes?

    Thanks,

    Kimball Johnson

  4. #4
    Join Date
    Jan 2003
    Posts
    4,126
    Yes, This will work in an SP:

    DECLARE stmt VARCHAR(2000);
    ...

    SET STMT = 'SELECT * from MyTable where ' || colname || ' in ' || list;
    PREPARE S1 FROM stmt;
    EXECUTE S1;
    ...

    Andy

  5. #5
    Join Date
    Aug 2004
    Posts
    51
    Great! Thanks!

  6. #6
    Join Date
    Aug 2004
    Posts
    51

    Wait a minute

    What about the need for cursors?

    When you EXECUTE the string version of the command, where do the records appear?

    Do you have to declare a cursor for return to client? And then Open it?

    Does the execute command return a table so that you could say,

    SET something = EXECUTE "select * from mytable" - ?

    ?

    Thanks,

    Kimball Johnson

  7. #7
    Join Date
    Jan 2003
    Posts
    4,126
    Sorry, forgot about cursor.

    DECLARE MyCursor CURSOR for S1;
    ...
    DECLARE stmt VARCHAR(2000);
    ...
    SET STMT = 'SELECT * from MyTable where ' || colname || ' in ' || list;
    PREPARE S1 FROM stmt;

    Open MyCursor;
    ...

    Andy

  8. #8
    Join Date
    Aug 2004
    Location
    London, UK
    Posts
    31
    Should be something like this:-

    DECLARE stmt VARCHAR(2000);
    DECLARE vchar1 varchar(100);
    ...

    SET STMT = 'SELECT vchar1 from MyTable where ' || colname || ' in ' || list;
    PREPARE S1 FROM stmt;
    DECLARE C1 CURSOR FOR S1;
    OPEN C1;
    FETCH C1 INTO vchar1;
    ....

    Just google for some examples
    jdey@macehill.co.uk
    http://www.macehill.co.uk

Posting Permissions

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