Stored procedure returning multiple rows

[ROBLEMMENS]

hi, im using Delphi6 and Interbase 6, i need a stored procedure that returns more than one row but whenever i try to create the procedure it says token unknown ; ?
This is my procedure:

CREATE PROCEDURE TEST_PROC(DOC_NR INTEGER)
RETURNS
AS
BEGIN
SELECT *
FROM DOCUMENT
WHERE NR = OC_NR;
END!!

i think that isql expects the INTO clausule but i read in the ib documentation that INTO can only be used for a singleton select. How do i make a procedure returning multiple rows? I can't use a query because it would be executed on the client side and we need it to execute on the server.

 

[Otto]

Here is a small example:

SET TERM ^ ;
CREATE PROCEDURE "INQUIRE"
(
"CNT" SMALLINT
)
RETURNS
(
"ID" INTEGER,
"NAME" VARCHAR(30)
)
AS
begin
for select ID, NAME from TABLE into :ID, :NAME do begin
suspend;
CNT=CNT-1;
if (CNT<=0) then exit;
end
end
^

SET TERM ; ^
COMMIT WORK;

 

[cheikh1]

you can write your stored procedure like that:
SET TERM !!;
CREATE PROCEDURE TEST_PROC(DOC_NR INTEGER)
AS
BEGIN
SELECT * FROM DOCUMENT WHERE
NR=OC_NR;
END;!!
SET TERM ;!!
but i advice you to use a query instead is the best choice i think.

 

[oguraja]

hi,

I miss the returning parameter(s)

CREATE PROCEDURE TEST_PROC(DOC_NR INTEGER)
RETURNS
(Param1 as Integer)
AS
BEGIN
for
SELECT Param1
FROM DOCUMENT
WHERE NR = OC_NR into aram1 do
begin
suspend;
end
END

You cannot use *, you always have to define the parameters.

But I also have problems with a correct working sp (in Delphi, via bde) with crystal reports.

I always get an error (tolken unkown, line 20 char0)

Raja