--- In ib-support@yahoogroups.com, Helen Borrie <helebor@t...> wrote:

> At 10:49 AM 27/01/2003 +0000, you wrote:
> >Hello,
> >
> >I am converting stored procedure from MSSQL to Firebird,
> >How to define the stored procedure in Firebird for
> >a stored procedure in MSSQL as
> >
> >CREATE PROCEDURE insertRecord
> > @name varchar(10),
> > @id int output
> >AS
> > INSERT INTO myTable (name) values(@name)
> > SELECT @id == @@IDENTITY
> >GO
>
> Assuming you have defined a generator for the identity column of

MyTable

> (let's call it gen_MyTable_ID) you would do something like this:
>
> SET TERM ^;
> CREATE PROCEDURE insertRecord (In_Name varchar(10))
> RETURNS (out_ID integer)
> as
> begin
> out_ID = GEN_ID(gen_MyTable_ID, 1);
> INSERT INTO myTable (id, name)
> VALUES (:out_ID, In_Name);
> end ^
> SET TERM ;^
>
> Normally, we don't get the generator value this way. If we need to

return

> the value to our application, we perform a query to get it *before*

we call

> the SP, and pass it as an input parameter.
>
> To read the ID from this stored procedure, you have two options:
> either
>
> 1) EXECUTE it with the statement
> EXECUTE PROCEDURE insertRecord(yourInputString)
>
> and read it from the parameter block returned when the procedure is

committed;

>
> or
>
> 2) make the procedure return the value as an output set, by calling

it with

> SELECT instead of EXECUTE, viz.
>
> SELECT ID FROM insertRecord(yourInputString)
>
> If you need to call this procedure from another procedure, you will

need to

> declare a variable in the top-level procedure, e.g.
> ...
> DECLARE VARIABLE returned_id integer;
> ...
> begin
> ...
> EXECUTE PROCEDURE insertRecord(yourInputString)
> RETURNING_VALUES(:returned_id);
> ...
> You need to get hold of either the Using Firebird manual (on the

IBPhoenix

> CD) or the InterBase 6 Language Reference (LangRef.pdf) as there is
> nothing standard about the way different vendors implement their stored
> procedure languages.
>
> heLen

Thank You Helen

CN