Unanswered: Passing table name as a input parameter in stored procedure or function (DB2 10.5)
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
DECLARE sqlstmt VARCHAR(400);
DECLARE CUR1 CURSOR WITH RETURN FOR sqlstmt1;
SET sqlstmt='SELECT SUM(INCM_AMT) FROM '||tabname ;
PREPARE sqlstmt1 FROM sqlstmt;
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
DECLARE v_dynSQL VARCHAR(200);
SET v_dynSQL=('SELECT * FROM '||TABNAME);
and I am receiving the following error
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
Dyamic SQL may add a whole new level of complexity, not only on DB2.
For example, please see posts on Microsoft SQL Server...
Originally Posted by Wim
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.
Originally Posted by Pat Phelan
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!
Please study more by yourself, following to db2mor's suggestion.