Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Posts
    118

    Unanswered: Set SQLCODE in SP

    Hi All !

    I am reviewing SQL stored procedures written using stored procedure builder.
    I noticed that they are all coded the following way

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
    SET l_sqlcode = SQLCODE
    SET l_sqlcode = 0

    DECLARE cur1 CURSOR FOR SELECT col1, col2 FROM tab1;
    OPEN cur1;
    FETCH cur1 INTO v1, v2;
    WHILE l_sqlcode = 0
    logic...
    FETCH
    END WHILE;

    Now my question is
    After the initial SET of l_sqlcode (SET l_sqlcode = 0), Is the variable l_sqlcode always set automatically by the SQLCODE ? Or do you have to explicitly assign the value of SQLCODE (after a SQL statement) to l_sqlcode ?
    Also, is there any reason to have a new variable l_sqlcode ? Can we not just check for SQLCODE after execution of every SQL statement ?
    when would you declare a HANDLER for SQLEXCEPTION,SQLWARNING,NOT FOUND ?
    and when would have a HANDLER just for SQLWARNING,NOT FOUND ?

    lastly, I would appreciate if someone has a template SP (using SQL SP) that I can use as a reference.
    thank you.

    Anil

  2. #2
    Join Date
    Nov 2004
    Posts
    374

    sp

    the handler is to easy : you don't have to check each time sqlcode. the handler will do. The problem is, the logic is the same for each condition.
    If no handler defined, sqlcode should be checked and defined.
    See appl progr guide how to check sqlcode in sql sp. there are some samples
    Best Regards, Guy Przytula
    Best Regards, Guy Przytula
    DB2/ORA/SQL Services
    DB2 DBA & Advanced DBA Certified
    DB2 Dprop Certified
    http://users.skynet.be/przytula/dbss.html

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    There is an example of SQL Procedure Handlers at
    http://www.db2click.com/scripts.htm

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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