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

Regarding a Stored Procedure with comma separated Value in 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
Share on Google+
4 Replies


100+
P: 1,646
Hi Hirak. This is the introductions forum. You are much more likely to get a response to your question if you post it in the DB2 forum
Thanks
Mar 13 '07 #2

bartonc
Expert 5K+
P: 6,596
Hi Hirak. This is the introductions forum. You are much more likely to get a response to your question if you post it in the DB2 forum
Thanks
Yeah. What Aric said. And besides, you've been here long enough to know that. What's up with that? And not using code tags?
Mar 13 '07 #3

100+
P: 1,646
Yeah. What Aric said. And besides, you've been here long enough to know that. What's up with that? And not using code tags?
Excuse me! Whilst I must consider my upgraded nomineclature to be a promotion of the most flattering ilk, I feel obliged to bring to your attention the notion that our brave Aric may not be equally jubilant.
Mar 13 '07 #4

hirak1984
100+
P: 316
Yes there is a problem,someone else is also using it.I have informed admins about it.Please ignore this post.Still I am sorry about it.
Hi Hirak. This is the introductions forum. You are much more likely to get a response to your question if you post it in the DB2 forum
Thanks
Mar 13 '07 #5

Post your reply

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