Hi,

> 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

This doesn't work. Use the following syntax:

execute statement sql;

Where "sql" is variable of type char or varchar, containing SQL
statement. This is not fast, so, if such execution is done inside loop,
it may take long time.


--
Aurimas