Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2013
    Location
    Chennai
    Posts
    2

    Unanswered: Passing table name as a input parameter in stored procedure or function (DB2 10.5)

    Hi everyone..

    I'm trying to pass a table name as a input parameter. I have tried this in both the Procedure and User defined functions.

    CREATE PROCEDURE SP (IN tabname VARCHAR(400))
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    DECLARE sqlstmt VARCHAR(400);
    DECLARE CUR1 CURSOR WITH RETURN FOR sqlstmt1;
    SET sqlstmt='SELECT SUM(INCM_AMT) FROM '||tabname ;
    PREPARE sqlstmt1 FROM sqlstmt;
    END P1

    If I provide the table name or column name statically I could able to get the result but when I passed the table name or column name dynamically I'm retrieving the output with the null value


    Name Type Data type Value Value (OUT)
    ------- ----- --------- ---------------------------------------- -----------
    tabname INPUT VARCHAR Sample


    How to fetch values from the table dynamically using the procedure?


    I also tried out in this way

    CREATE PROCEDURE DYNAMIC_TABLE (IN TABNAME VARCHAR(400))
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    DECLARE v_dynSQL VARCHAR(200);
    SET v_dynSQL=('SELECT * FROM '||TABNAME);
    EXECUTE v_dynSQL;
    END P1

    and I am receiving the following error

    Run: ISBA.DYNAMIC_TABLE(VARCHAR(400))

    {call ISBA.DYNAMIC_TABLE(?)}
    The statement named in the EXECUTE statement is not in a prepared state or is a SELECT or VALUES statement.. SQLCODE=-518, SQLSTATE=07003, DRIVER=4.16.53
    Run of routine failed.
    - Roll back completed successfully.

    If anyone can explain with an example for this problem it would be better. Thanks in advance

    Regards,
    Ramc Natarajan

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    You should study the sample stored procedures carefully - they are in the SAMPLES tree of your db2-server, and they are also on-line in the Knowledge Centre.

    Fold your names to uppercase.
    Don't assume the schema name.
    Don't use EXECUTE if you mean EXECUTE IMMEDIATE.

    Do spend the time learning the basics from the samples, get the samples working.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Dyamic SQL may add a whole new level of complexity, not only on DB2.

    For example, please see posts on Microsoft SQL Server...
    Quote Originally Posted by Wim View Post
    ...
    ...

    Only uncomment
    --EXEC(@SQL)
    and comment out
    PRINT @SQL
    once you are 100% sure the query works (like you have copy and pasted it into SSMS and it gives you the results you expect). This way you will notice all errors in the generated SQL script.
    In the example I gave, there is a space missing between "FROM table1"' and "WHERE col1". Only with a PRINT it becomes obvious.

    And avoid dynamic SQL whenever possible.
    and
    Quote Originally Posted by Pat Phelan View Post
    To emphasize what Wim said: avoid dynamic SQL if possible!

    Pay attention to the rest of what Wim said, but he didn't emphasize that point nearly enough to suit me!

    -PatP
    Please study more by yourself, following to db2mor's suggestion.

  4. #4
    Join Date
    Jun 2013
    Location
    Chennai
    Posts
    2

    Passing table name as a input parameter in stored procedure or function (DB2 10.5)

    Hi everyone..

    Thank you for your replies.

    I am much clear in the basics.If I pass the column value dynamically then my procedure could get the result but while passing the column name dynamically only i am facing this kind of issue .

    Avoiding the Dynamic SQL is a good idea but i want to know how it pass the input dynamically

    please guide me with the link for SAMPLES tree on the DB2 Server or send as the sample procedure for the scenario
    Last edited by Ramc Natarajan; 11-01-14 at 15:38.

Posting Permissions

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