Hello Rick,

>>However, IMO, it's _very_ bad practice to do a "select from
>>myprocedure" that updates/inserts data. Only use select-able
>>stored procedures for procedures that simply return data without
>>modifying it.
>
>Could you explain that? I'm using an almost identical technique - one I'm
>pretty sure I picked up from some of the Firebird or Interbase
>documentation I was waist high in yesterday. I'd like to understand better
>why this is bad design.

It's like quantum mechanics: you can't "look" (= select) something
without modifying it? This is, as I said, IMO, counter intuitive.

It's like a Delphi "get method" to get the value of a property that does
all sorts of stuff and modifies the object behind the screens. Do you
code that as well? I doubt it.

If you are select-ing, return data, don't modify it.

>My approach is almost identical, except I return 2 values. An ID and
>whether an insert occurred.

Also, let me go through your code :-)

>PROCEDURE FIND_OR_ADD_ARTIST(
> NAME CHAR(100) CHARACTER SET NONE)
>RETURNS(
> ARTISTID BIGINT,
> ADDED SMALLINT)
>AS
>DECLARE VARIABLE FOUNDID BIGINT;
>BEGIN
> FoundID = -1;
> Added=0;
> SELECT ID FROM ARTISTS WHERE (Artists.Name = :NAME) INTO :FoundID;
> if (:FoundID = -1) THEN
> BEGIN
> INSERT INTO Artists (Name) VALUES (:NAME);
> SELECT gen_id(ARTISTS_ID_GEN,0) FROM RDB$DATABASE INTO :FoundID;

This isn't multi-user safe.

If someone else uses this procedure/code, an INSERT could occur between
your INSERT and the next SELECT. You will get the wrong ID here, because
the generated has been incremented.

> Added=1;
> END
> ArtistID = :FoundID;
> SUSPEND;
>END
>

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com