i think you are missing about "SUSPEND" statement at your loop, try this :
create or alter procedure sel_product_by_product_id
(
product_id int
)
......
begin
select ....
from
product
where
product_id = :product_id
into ...;
suspend;
end

----- Original Message -----
From: Storage Box
To: firebird-support@yahoogroups.com
Sent: Thursday, December 09, 2004 9:20 AM
Subject: [SPAM]: [firebird-support] Simple stored procedure won't work


I'm back with another lame question, I'm seriously stumped here!

I have a simple sproc that selects records from a table, filtered by a
parameter passed into it. I can do a regular select statement, copied right
out of the sproc itself and it returns records. But...when I execute the
stored procedure, I get *nothing*.

-- The stored procedure

create or alter procedure sel_product_by_product_id
(
product_id int
)
returns
(
product_id_ int,
product_name_ varchar(50),
product_num_ varchar(50),
synopsis_ varchar(500),
description_ varchar(1000),
wholesale_price_ decimal(9, 2),
unit_price_ decimal(9, 2),
sale_price_ decimal(9, 2),
weight_ decimal(9, 2),
height_ decimal(9, 2),
width_ decimal(9, 2),
length_sz_ decimal(9, 2),
insured_value_ decimal(9, 2),
is_free_shipping_ char,
shipping_amt_ decimal(9, 2),
is_cross_sell_ char,
thumbnail_ varchar(100),
photo_ varchar(100)
)
AS
begin
select
product_id,
product_name,
product_num,
description,
synopsis,
wholesale_price,
unit_price,
sale_price,
weight,
height,
width,
length_sz,
insured_value,
is_free_shipping,
shipping_amt,
is_cross_sell,
thumbnail,
photo
from
product
where
product_id = :product_id
into
:product_id_,
:product_name_,
:product_num_,
:synopsis_,
:description_,
:wholesale_price_,
:unit_price_,
:sale_price_,
:weight_,
:height_,
:width_,
:length_sz_,
:insured_value_,
:is_free_shipping_,
:shipping_amt_,
:is_cross_sell_,
:thumbnail_,
:photo_;
end
^

-- Executed as follows, returns no records, even though there is a record
with a product_id of 2.

select * from SEL_PRODUCT_BY_PRODUCT_ID(2);

However, I can run this query and get the record:

select
product_id,
product_name,
product_num,
description,
synopsis,
wholesale_price,
unit_price,
sale_price,
weight,
height,
width,
length_sz,
insured_value,
is_free_shipping,
shipping_amt,
is_cross_sell,
thumbnail,
photo
from
product
where
product_id = 2

What gives? Am I missing something obvious? I'm rather new to Firebird but
this should be pretty straight forward.

Regards,

Vincent Jenks
www.scientifik.com <http://www.scientifik.com/>

******************************************************************
This message is confidential. It may also be privileged or otherwise
protected by work product immunity or other legal rules. You may not copy
this message or disclose its contents to anyone. The integrity and security
of this message cannot be guaranteed on the Internet.



[Non-text portions of this message have been removed]


Yahoo! Groups Sponsor

Get unlimited calls to

U.S./Canada




------------------------------------------------------------------------------
Yahoo! Groups Links

a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

b.. To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.




[Non-text portions of this message have been removed]