Re: [Firebird-Java] stored procedure returns no rows
Author
Phil Shrimpton
Post date
2003-06-14T12:26:21Z
On Saturday 14 June 2003 12:12, john14v6 wrote:
Hi,
You are best off posting non-java specific Firebird questions to the main
support list at firebird-support@yahoogroups.com, but..
> Could someone tell me why this simple stored procedure
> is returning no rows, even though I know for sure it
> should return rows for the id I pass in?
Because you have created an 'execute' that will at most return a single
record, and must be called by issuing 'EXECUTE instead of SELECT
If you want to return more than one record, you will need to make the
following changes...
> SET TERM !! ;
> CREATE PROCEDURE SP_GET_USER (v_user_id INTEGER)
> RETURNS (r_id INTEGER, r_first_name VARCHAR(20),
> r_mi CHAR(1), r_last_name VARCHAR(30))
> AS
> BEGIN
FOR
> SELECT id, first_name, mi, last_name FROM USERS
> WHERE id = :v_user_id
> INTO :r_id, r_first_name, r_mi, r_last_name;
DO
SUSPEND;
> END !!
> SET TERM ; !!