Failed to create stored procedure in Firebird

I am trying firebird on my new project but stuck on how to create stored procedures. There is no luck either from .NET provider or from the admin tool "IB_SQL".

here is what I did:

the Table is simple one like this:
CITY (ID integer, NANE varchar(100))

and the Stored Procedure is as following:

CREATE PROCEDURE sp_City (CITY VARCHAR(100))
RETURNS (CITYID INTEGER)
AS
BEGIN
SELECT ID FROM CITY WHERE NAME=:CITY INTO :CITYID;
--I tried as follow too, does not work either:
--SELECT ID INTO :CITYID FROM CITY WHERE NAME=:CITY;
/*
IF (CITYID IS NULL) THEN
BEGIN
CITYID = GEN_ID(GID_CITY,1);
INSERT INTO CITY (ID, NAME) VALUES (:CITYID,:CITY);
END
*/
END



hope someone can help me out.
many thanks.

What's the error message you're getting?

--
Martijn Tonies
Upscene Productions
www.upscene.com

Hi Martijn,

Thanks for your response.

When I tried like this I got Unsupported column type: 0 error.

CREATE PROCEDURE sp_City (CITY VARCHAR(100))
RETURNS (CITYID INTEGER)
AS
BEGIN
SELECT ID FROM CITY WHERE NAME=:CITY INTO :CITYID;
/*
IF (CITYID IS NULL) THEN
BEGIN
CITYID = GEN_ID(GID_CITY,1);
INSERT INTO CITY (ID, NAME) VALUES (:CITYID,:CITY);
END
*/
END

if I change to the following I got 'mn 16' is not a valid integer value error.

CREATE PROCEDURE sp_City (CITY VARCHAR(100))
RETURNS (CITYID INTEGER)
AS
BEGIN
SELECT ID INTO :CITYID FROM CITY WHERE NAME=:CITY;
/*
IF (CITYID IS NULL) THEN
BEGIN
CITYID = GEN_ID(GID_CITY,1);
INSERT INTO CITY (ID, NAME) VALUES (:CITYID,:CITY);
END
*/
END

and the table I was using is CITY (ID integer, NAME varchar(100))

I am new to Firebird and it must be something really silly I did, but I just could not find it, the procedure is so simple and in fact
I just copied from the document.

Appreciated for your help.

It seems you're trying to create the procedure from the DSQL tab in IB_SQL or something?

Try the Script tab, or make sure to NOT check for parameters on the DSQL tab.

Thanks for your help.

You are right, I was using the ID_SQL's DSQL tab to create the stored procedure, and when I ticked off the parameter check box, it works.

BTW, any better admin tools you could recommend?

The tool we develop is much better

Database Workbench: www.upscene.com

--
Martijn