By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,513 Members | 1,640 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,513 IT Pros & Developers. It's quick & easy.

Regarding the Stored procedure with comma separated input

hirak1984
100+
P: 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.TempList
--declare table AUS.TempList
( OrderID CHAR(6)
)
WITH replace on commit preserve rows not logged in SESSION_TMP_TBSP --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(var0, ',', ''));
IF @temp <> ''
THEN
WHILE @Pos > 0
DO
SET @OrderID = LTRIM(RTRIM(LEFT(var0, @Pos - 1)));
IF @OrderID <> ''
THEN
INSERT INTO SESSION.TempList (OrderID) VALUES (@OrderID); --Use Appropriate conversion
END IF;

SET var0 = RIGHT(var0, (LENGTH(var0) - @Pos)) ;


END WHILE;
END IF ;

--select SESSION.TempList.OrderID into var1 from SESSION.TempList,AUS.ITM_T where SESSION.TempList.OrderID<>AUS.itm_t.itm_nbr ;
END P1


----------------------------------------------------------------
This procedure is executed fine.
But when we will give the input 1111,1234,...,That time error comes.

Please Anybody who can help me as review this code & where exactly error comes.
Mar 13 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.