473,320 Members | 2,164 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Regarding a Stored Procedure with comma separated Value in input

hirak1984
316 100+
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
4 4097
willakawill
1,646 1GB
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
6,596 Expert 4TB
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
willakawill
1,646 1GB
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
316 100+
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

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

Similar topics

7
by: Tod Thames | last post by:
I am running SQL Server 7.0 and using a web interface. I would like for a user to be able to input multiple values into a single field with some sort of delimiter (such as a comma). I want to...
0
by: Nashat Wanly | last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET View products that this article applies to. This article was previously published under Q310070 For a Microsoft...
4
by: erich93063 | last post by:
I have a stored procedure which is performing a search against a "task" table. I would like to pass in a variable called @strAssignedTo which will be a comma delimeted list of employee ID's ie:...
2
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
4
by: nicholas | last post by:
Could someone tell me how to implement an INSERT on an aspx-page using a stored procedure, in VB-code? THX, Nic PS: I have been trying this, but it ain't working: Sub insert_new_content...
7
by: JIM.H. | last post by:
Hello, Is there any difference to between SLQ string in the code and call execute query and call a stored procedure and execute the query that way concerning speed, effectiveness, reliability,...
2
by: karups | last post by:
Hi, I've got listbox in my .aspx page where the users can make multiple selection. So, Users can select any number of items in listbox, I have to take value from items and pass it to stored...
0
hirak1984
by: hirak1984 | last post by:
Hi, I am passing an array of comma separated values to a stored procedure. I have to receive them as varchar. but inside I want to get individual integers for some manipulations. How can I get...
0
hirak1984
by: hirak1984 | last post by:
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...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.