Subject Re: Stored procedure help
Author Svein Erling
> Hi All,
> First post so please take it easy on me:)

You've started your Firebird adventure very well Sandy, so there's not much reason to be hard on you. Well, that is, with one exception: You should really start a new thread for a new question rather than hit reply to an old question - that can mess up how things are sorted for people trying to 'thread' subjects. I've started a new thread with my reply, even though the subject has the same name as your original email.

> I would like to create stored procedure that will update all values held in fields defined with domain of Domain_Name. I will pass Domain_Name to the stored procedure.
> I already have:-
> /**** Start of SQL Code ****/
> SET TERM ^ ;
> create or alter procedure CASCADE_FIELD (
> DOMAIN_NAME varchar(100),
> OLD_VALUE varchar(100),
> NEW_VALUE varchar(100))
> as
<presumably working code skipped>
> do begin
> :table_name /*** Errors Here ****/
> :field_name = :new_value
> :field_name = :old_value
> end
> suspend;
> end^
> Invalid token.
> Dynamic SQL Error.
> SQL error code = -104.
> Token unknown - line 30, char 7.
> :.

The reason for this is very simple. The code is parsed upon creation of the stored procedure and you need to know the name of the table and field at that time. Parameters can only be used for field VALUES, not table or field NAMES.

> What can I do to fix it?.

Firebird do support, well, maybe it's called late binding? But you do this similar to (assuming s to be a CHAR variable of sufficient size):

s = 'UPDATE ' || table_name ||
' SET ' || field_name || '=' || new_value ||
' WHERE ' || field_name || '=' || old_value;

> Also if anyone knows of anywhere on the net I can find some good
> examples of FireBird Stored Procedures, it would be a great help.

I've never written complicated stored procedures, so I don't know. Hopefully others can answer this bit.