473,324 Members | 2,193 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,324 software developers and data experts.

Need help with Stored Procedure

Chris Eaton gave an example of stored procedure transpose columns to rows
Here is an example of a rowtocol stored proc that takes a SQL statement as
the first paramter, a delimiter as the second parameter and the ouput (in the
3rd parameter) is the rows coverted to a column with the delimiter specified
used to separate the row values:

CREATE PROCEDURE rowtocol
(IN p_slct VARCHAR(4000), IN p_dlmtr VARCHAR(4000), OUT lc_str VARCHAR(4000))
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5);
DECLARE lc_colval VARCHAR(4000);
DECLARE c_refcur INT;
DECLARE at_end INT DEFAULT 0;

DECLARE not_found CONDITION FOR SQLSTATE '02000';

DECLARE C1 CURSOR FOR S1;
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;

PREPARE S1 FROM p_slct;

SET lc_str = '';
OPEN C1;
fetch_loop:
LOOP
FETCH C1 INTO lc_colval;
IF at_end = 1 THEN LEAVE fetch_loop;
END IF;
SET lc_str = lc_str || p_dlmtr || lc_colval;
END LOOP;
CLOSE C1;
END

When i try to use it
CALL ROWTOCOL('SELECT NAME, DOB FROM FAMILY',',');
i got the following error:
sqlcode: -440
The parameter mode (IN, OUT, or INOUT) is not valid for a parameter in
procedure.

How to fix my call?
Thank's in advance.

--
Message posted via http://www.dbmonster.com

Mar 12 '08 #1
2 1878
"lenygold via DBMonster.com" <u41482@uwewrote in message
news:810ffe956a9f2@uwe...
Chris Eaton gave an example of stored procedure transpose columns to rows
Here is an example of a rowtocol stored proc that takes a SQL statement as
the first paramter, a delimiter as the second parameter and the ouput (in
the
3rd parameter) is the rows coverted to a column with the delimiter
specified
used to separate the row values:

CREATE PROCEDURE rowtocol
(IN p_slct VARCHAR(4000), IN p_dlmtr VARCHAR(4000), OUT lc_str
VARCHAR(4000))
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5);
DECLARE lc_colval VARCHAR(4000);
DECLARE c_refcur INT;
DECLARE at_end INT DEFAULT 0;

DECLARE not_found CONDITION FOR SQLSTATE '02000';

DECLARE C1 CURSOR FOR S1;
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;

PREPARE S1 FROM p_slct;

SET lc_str = '';
OPEN C1;
fetch_loop:
LOOP
FETCH C1 INTO lc_colval;
IF at_end = 1 THEN LEAVE fetch_loop;
END IF;
SET lc_str = lc_str || p_dlmtr || lc_colval;
END LOOP;
CLOSE C1;
END

When i try to use it
CALL ROWTOCOL('SELECT NAME, DOB FROM FAMILY',',');
i got the following error:
sqlcode: -440
The parameter mode (IN, OUT, or INOUT) is not valid for a parameter in
procedure.

How to fix my call?
Thank's in advance.
Try this:
CALL ROWTOCOL('SELECT NAME, DOB FROM FAMILY',''',?);

or this
CALL ROWTOCOL('SELECT NAME, DOB FROM FAMILY','@',?);
Mar 12 '08 #2
Thank You it is working.

Mark A wrote:
>Chris Eaton gave an example of stored procedure transpose columns to rows
Here is an example of a rowtocol stored proc that takes a SQL statement as
[quoted text clipped - 42 lines]
>How to fix my call?
Thank's in advance.

Try this:
CALL ROWTOCOL('SELECT NAME, DOB FROM FAMILY',''',?);

or this
CALL ROWTOCOL('SELECT NAME, DOB FROM FAMILY','@',?);
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200803/1

Mar 12 '08 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Shawn Fletcher | last post by:
Hi, I'm trying to work around a bug that our helpdesk software has. When a new issue is created, it cannot automatically default 2 fields to the value of No like we need it to. I have a...
5
by: Ralph | last post by:
Hi all, I'm a newbie to MS-SQL UDFs and seem to have a real big problem. I need to implement a logic to receive an adress build out of various user definable fields from various user defined...
0
by: Doug R | last post by:
Hello, I have a system that I am writing to automaticly import Credit Transaction data into a SQL Server 2000 Database. I am using a VB.Net application to detect when the file arives and prep...
4
by: Rhino | last post by:
Is it possible for a Java Stored Procedure in DB2 V7.2 (Windows) to pass a Throwable back to the calling program as an OUT parameter? If yes, what datatype should I use when registering the...
1
by: Raquel | last post by:
Have a question on the Stored procedure method code generated by DB2 development center for Java stored procedures. Suppose I have a requirement to return the resultset consisting of FIRSTNME,...
3
by: Ellie O'Donnell | last post by:
Hi, I wrote a DB2 stored procedure in COBOL, and now I need to prepare a CLIST in order to call it. This CLIST would be for people who want to use the stored procedure outside of COBOL or Pl/I....
1
by: Jim H | last post by:
I am on a project where I am supposed to send an XML document to a SQL Server stored procedure. The XML Doc is a list strings. If in my c# function I get a list values as (string psValueList),...
3
by: Jack Black | last post by:
Help!! I'm trying to call a custom stored procedure from a VB.Net code-behind page in an ASP.Net application, and I keep getting an error with no real helpful info... Basically, I'm accepting a...
5
by: byahne | last post by:
We just went live today with a production SQL Server 2005 database running with our custom Java application. We are utilizing the jTDS open source driver. We migrated our existing application...
0
by: SOI_0152 | last post by:
Hi all! Happy New Year 2008. Il hope it will bring you love and happyness I'm new on this forum. I wrote a stored procedure on mainframe using DB2 7.1.1 and IBM language c. Everything works...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
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

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.