#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Posts
    2
    Rep Power
    0

    New DB2 Developer


    Hi Guys,

    I am new to DB2 Development, i am asked to write a stored procedure with 3 IN parameters which when entered by the user, the values should be replaced with the field values in the select statement and then the result of the same should be inserted in the temp table.

    Please let me know some useful tricks to create the same. Hope you all get what i am trying to do.

    Thanks in advance,
    ALOK
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Posts
    2
    Rep Power
    0
    I have written the following code... but its giving me the error below. Please response quick.


    CREATE PROCEDURE CARDIFF (
    @STARTDATE DATE,
    @ENDDATE DATE,
    @CARDTYPEID INT,
    OUT @ERRORDESCRIPTION VARCHAR(200),
    OUT @a INT,
    OUT @b INT,
    OUT @c INT,
    OUT @d INT,
    OUT @e INT,
    OUT @f INT,
    OUT @g INT,
    OUT @h INT,
    OUT @i INT,
    OUT @j INT)
    LANGUAGE SQL
    MODIFIES SQL DATA
    DYNAMIC RESULT SETS 1




    P1: BEGIN
    DECLARE SQLCODE INT DEFAULT 0
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000'
    DECLARE RETCODE INT DEFAULT 0
    DECLARE RETSTATE CHAR(5) DEFAULT '00000'
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING
    BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING
    GET DIAGNOSTICS EXCEPTION 1 @ERRORDESCRIPTION=MESSAGE_TEXT;
    VALUES(SQLSTATE, SQLCODE) INTO RETSTATE, RETCODE
    RESIGNAL;
    END;


    select count(1) INTO @a from escrip.member where terminationdate is null
    and memberid in (select memberid from escrip.membercard where terminationdate is null and cardtypeid = @CARDTYPEID)
    and Date(entrydate) between @STARTDATE and @ENDDATE
    and firstnamecp NOT in ('UNKNOWNCARD','UNKNOWNPHONE');


    select count(1) INTO @b from escrip.member where terminationdate is null
    and length(phone) = 10
    and memberid in (select memberid from escrip.membercard where terminationdate is null and cardtypeid = @CARDTYPEID)
    and Date(entrydate) between STARTDATE' and @ENDDATE
    and firstnamecp NOT in ('UNKNOWNCARD','UNKNOWNPHONE');


    select count(1) INTO @c from escrip.member where terminationdate is null
    and length(email) > 5
    and memberid in (select memberid from escrip.membercard where terminationdate is null and cardtypeid = @CARDTYPEID)
    and Date(entrydate) between @STARTDATE and @ENDDATE
    and firstnamecp NOT in ('UNKNOWNCARD','UNKNOWNPHONE');


    select count(1) INTO @d from escrip.member where terminationdate is null
    and memberid in (select memberid from escrip.membercard where terminationdate is null and cardtypeid = @CARDTYPEID)
    and Date(entrydate) between @STARTDATE and @ENDDATE
    and firstnamecp in ('UNKNOWNCARD');


    select count(1) INTO @e from escrip.member where terminationdate is null
    and memberid in (select memberid from escrip.membercard where terminationdate is null and cardtypeid = @CARDTYPEID)
    and Date(entrydate) between STARTDATE and @ENDDATE
    and firstnamecp in ('UNKNOWNPHONE');


    select count(1) INTO @ffrom escrip.member where terminationdate is null
    and memberid in (select memberid from escrip.membercard where terminationdate is null and cardtypeid = @CARDTYPEID)
    and firstnamecp not in ('UNKNOWNCARD','UNKNOWNPHONE');


    select count(1) INTO @g from escrip.member where terminationdate is null
    and length(phone) = 10
    and memberid in (select memberid from escrip.membercard where terminationdate is null and cardtypeid = @CARDTYPEID)
    and firstnamecp not in ('UNKNOWNCARD','UNKNOWNPHONE');


    select count(1) INTO @h from escrip.member where terminationdate is null
    and length(email) > 5
    and memberid in (select memberid from escrip.membercard where terminationdate is null and cardtypeid = @CARDTYPEID)
    and firstnamecp not in ('UNKNOWNCARD','UNKNOWNPHONE');


    select count(1) INTO @i from escrip.member where terminationdate is null
    and memberid in (select memberid from escrip.membercard where terminationdate is null and cardtypeid = @CARDTYPEID)
    and firstnamecp in ('UNKNOWNCARD');


    select count(1) INTO @j from escrip.member where terminationdate is null
    and memberid in (select memberid from escrip.membercard where terminationdate is null and cardtypeid = @CARDTYPEID)
    and firstnamecp in ('UNKNOWNPHONE');


    RETURN RETCODE;
    END P1
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0010N The string constant beginning with "'); RETURN RETCODE; END P1"
    does not have an ending string delimiter. LINE NUMBER=46. SQLSTATE=42603

    SQL0010N The string constant beginning with "');


    RETURN RETCODE;
    END P1 " does not have an ending string delimiter.

    Explanation:

    The statement contains a string constant, beginning with
    "<string>", that is not terminated properly.

    The statement cannot be processed.

    User Response:

    Examine the statement for missing apostrophes in the indicated
    string constant.

    sqlcode : -10

    sqlstate : 42603

IMN logo majestic logo threadwatch logo seochat tools logo