On 8-8-2014 20:53, jim.rhema316@... [firebird-support] wrote:

> The stored procedure is supposed to match the input vin to a vin in
> another table. Then within the SP I want to update a different table
> with the values retrieved from the first select.
>
> I am executing a stored procedure and I am getting this error:
>
> 14:44:27 [SELECT - 0 row(s), 0.000 secs] [Error Code: 335544343, SQL
> State: HY000] GDS Exception. 335544343. invalid request BLR at offset 49
>
> No message for code 335544868 found.
>
> null

Note that 'No message for code 335544868' means that you use an outdated
firebird.msg file client side.

The error message for 335544868 is:
Procedure {0} is not selectable (it does not contain a SUSPEND statement)
(where {0} would be the name of the stored procedure).

> I can't find any information on what is causing this error.
>
> Here is the SP:
>
> CREATE or Alter PROCEDURE GetBikeUpdate
> ( In_Vin varchar(8))
> RETURNS
> ( Out_BikeYear varchar(50),
> & nbsp;Out_BikeMake varchar(50),
> Out_BikeModel varchar(23),
> Out_BikeVin varchar(50),
> Out_BikeLast8Vin varchar(8))
> AS
> --DECLARE VARIABLE variable_name < datatype>;
> BEGIN
> /* write your code here */
> FOR SELECT BikeYEAR, BikeMAKE, BikeMODEL, vin, LAST8VIN FROM
> BIKEHISTORY_LISTDATE
> WHERE :In_vin = last8vin
> INTO :Out_BikeYear, :Out_BikeMake, :Out_BikeModel, :Out_BikeVin,
> :Out_BikeLast8Vin
> DO
> BEGIN
> If (:In_vin = :Out_BikeLast8Vin) then BEGIN
> Update Total_sales_to_part_cost_match
> set TSPM_BIKEYEAR = :Out_BikeYear,TSPM_BIKEMAKE =
> :Out_BikeMake, TSPM_BIKEMODEL = :Out_BikeModel, TSPM_LAST8VIN =
> :Out_BikeLast8Vin;
> End
> End
> END;
>
> Here is the statement to execute the SP.
>
> select * from GetBikeUpdate ('7M005597');
>
> Any help or direction would be greatly appreciated. This is my first
> attempt at SP in Firebird so any guidance/correction is also appreciated.

The problem is that there are two types of stored procedures in Firebird:
* Selectable, which can return zero, one or more rows. These are
executed using SELECT ... FROM mystoredprocedure(...)
* Executable, which can return at most one row of data. These are
executed using EXECUTE PROCEDURE mystoredprocedure(...)

The difference between selectable and executable is the use of the
SUSPEND statement when a row needs to be emitted. When you try to use an
executable as a selectable, you get this error.

In short: you are executing as selectable, but your stored procedure is
executable.

You either need to use:

EXECUTE PROCEDURE GetBikeUpdate('7M005597');

or you need to add a SUSPEND as Dixon already explained. Although
personally I would put the suspend within the IF-block and not at the
end, especially if that select - contrary to my expectations - returns
multiple rows.

Given that your stored procedure will update (and return) at most one
row (as far as I understand your domain) and this is something that
changes data (instead of only producing rows), I would keep it an
executable stored procedure and change the way you execute it.

Mark
--
Mark Rotteveel