Need help with Stored Procedure

[lenygold via DBMonster.com]

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('SELEC T 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

[Mark A]

Re: Need help with Stored Procedure

"lenygold via DBMonster.com" <u41482@uwewrot e in message
news:810ffe956a 9f2@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('SELEC T 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('SELEC T NAME, DOB FROM FAMILY',''',?);

or this
CALL ROWTOCOL('SELEC T NAME, DOB FROM FAMILY','@',?);

[lenygold via DBMonster.com]

Re: Need help with Stored Procedure

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('SELEC T NAME, DOB FROM FAMILY',''',?);
>
>or this
>CALL ROWTOCOL('SELEC T NAME, DOB FROM FAMILY','@',?);

--
Message posted via DBMonster.com

http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200803/1