Topic
IC4NOTICE: 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.
2 replies Latest Post - ‏2012-05-24T15:10:39Z by ZarykJag
ZarykJag
ZarykJag
6 Posts
ACCEPTED ANSWER

Pinned topic as400 db2 7R1 - SQL Stored Procedures

‏2012-05-24T11:39:41Z |
as400 db2 7R1 - SQL Stored Procedures

DECLARE C1 INSENSITIVE CURSOR WITH RETURN
FOR
@SQL;
//dynamic sql
PREPARE @SQL FROM @SQLTemp;
OPEN C1;
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
Updated on 2012-05-24T15:10:39Z at 2012-05-24T15:10:39Z by ZarykJag
  • B.Hauser
    B.Hauser
    250 Posts
    ACCEPTED ANSWER

    Re: as400 db2 7R1 - SQL Stored Procedures

    ‏2012-05-24T12:49:48Z  in response to ZarykJag
    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.

    Birgitta
  • ZarykJag
    ZarykJag
    6 Posts
    ACCEPTED ANSWER

    Re: as400 db2 7R1 - SQL Stored Procedures

    ‏2012-05-24T15:10:39Z  in response to ZarykJag
    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.