316
Dear Sir/mam,
I am new comers for DB2 .I Write a Stored Procedure below in line
CREATE PROCEDURE AUS.PROCEDURE1 ( IN var0 VarCHAR(4000),
OUT var1 CHAR(6) )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
-- var0
-- var1
------------------------------------------------------------------------
P1: BEGIN
DECLARE @temp CHAR(4);
DECLARE @OrderID char(100);
DECLARE @Pos int DEFAULT 0;
DECLARE @count int DEFAULT 0;
DECLARE var1_TMP VARCHAR(4000) DEFAULT ' ';
-- CREATE USER TEMPORARY TABLESPACE tab_temp;
declare GLOBAL temporary table SESSION.TempLis t
--declare table AUS.TempList
( OrderID CHAR(6)
)
WITH replace on commit preserve rows not logged in SESSION_TMP_TBS P --DECLARE GLOBAL temporary table <name> (<params>) WITH replace on commit preserve rows not logged in <tblspc_name>
;
SET @Pos = posstr(var0,',' ) ;
SET @temp = CHAR(REPLACE(va r0, ',', ''));
IF @temp <> ''
THEN
WHILE @Pos > 0
DO
SET @OrderID = LTRIM(RTRIM(LEF T(var0, @Pos - 1)));
IF @OrderID <> ''
THEN
INSERT INTO SESSION.TempLis t (OrderID) VALUES (@OrderID); --Use Appropriate conversion
END IF;
SET var0 = RIGHT(var0, (LENGTH(var0) - @Pos)) ;
END WHILE;
END IF ;
--select SESSION.TempLis t.OrderID into var1 from SESSION.TempLis t,AUS.ITM_T where SESSION.TempLis t.OrderID<>AUS. itm_t.itm_nbr ;
END P1
----------------------------------------------------------------
This procedure is executed fine.
But when we will give the input 1111,1234,...,T hat time error comes.
Please Anybody who can help me as review this code & where exactly error comes.
