Hi All,

First post so please take it easy on me:)

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
declare variable TABLE_NAME varchar(100);
declare variable FIELD_NAME varchar(100);
begin
/* Validation */
if (:domain_name is null) then exception NULL_PARAM;
if (:domain_name = '') then exception EMPTY_PARAM;
if (:old_value is null) then exception NULL_PARAM;
if (:old_value = '') then exception EMPTY_PARAM;
if (:new_value is null) then exception NULL_PARAM;
if (:new_value = '') then exception EMPTY_PARAM;

FOR SELECT
RDB$RELATION_NAME,
RDB$FIELD_NAME
FROM
RDB$RELATION_FIELDS
WHERE
RDB$FIELD_SOURCE = :domain_name AND
RDB$RELATION_NAME NOT LIKE 'ZA_%'
INTO
:table_name, :field_name

do begin
UPDATE
:table_name /*** Errors Here ****/
SET
:field_name = :new_value
WHERE
:field_name = :old_value
end


suspend;
end^

SET TERM ; ^

GRANT EXECUTE ON PROCEDURE CASCADE_FIELD TO SYSDBA;
/**** End of SQL Code ****/


Would anyone be most kind to explain why it errors with:

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 30, char 7.
:.

What can I do to fix it?.

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.

TIA,
Sandy