as400 db2 7R1 - SQL Stored Procedures
DECLARE C1 INSENSITIVE CURSOR WITH RETURN
PREPARE @SQL FROM @SQLTemp;
set result sets cursor C1;
Is there a way to pass parameters to the cursor as if it were an "EXECUTE @SQL Using @Param1, @Param2
NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
This topic has been locked.
2 replies Latest Post - 2012-05-24T15:10:39Z by ZarykJag
Pinned topic as400 db2 7R1 - SQL Stored Procedures
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-05-24T15:10:39Z at 2012-05-24T15:10:39Z by ZarykJag
B.Hauser 1000007U1D250 PostsACCEPTED ANSWER
Re: as400 db2 7R1 - SQL Stored Procedures2012-05-24T12:49:48Z in response to ZarykJagThe parameters must be integrated as parameter markers (?) in your SQL String
If so try:
Create or Replace MyProc (In Parm1 VarChar(10), In Parm2 Int, ....) .... Declare PrvSQLCode SmallInt; DECLARE CusrorNotOpen Condition for '24501'; DECLARE C1 INSENSITIVE CURSOR WITH RETURN FOR @SQL; Declare Continue Handler For CursorNotOpen Set PrvSQLCode = SQLCode; //dynamic sql PREPARE @SQL FROM @SQLTemp; CLOSE C1; OPEN C1 Using Par1, Par2, ... ParmN;
BTW SET RESULT SET is no longer necessary!
... and avoid dynamic SQL if not needed.
ZarykJag 270005C6BY6 PostsACCEPTED ANSWER
Re: as400 db2 7R1 - SQL Stored Procedures2012-05-24T15:10:39Z in response to ZarykJagAwesome! The only reason we are using dynamic sql is because a previous programmer, who has already left, creates relationships between the asp.net controls and strongly typed properties that have attributes to automatically create the conditions, and pass them all at once to a stored procedure as a string. Not really the way I prefer it, but we are a small company and don't have a lot of time to change 5 or 6 applications that already use this method.