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