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

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