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
B.Hauser 1000007U1D298 Posts
Re: as400 db2 7R1 - SQL Stored Procedures2012-05-24T12:49:48ZThis is the accepted answer. This is the accepted answer.The 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 Posts
Re: as400 db2 7R1 - SQL Stored Procedures2012-05-24T15:10:39ZThis is the accepted answer. This is the accepted answer.Awesome! 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.