Dynamically create a Stored Procedure to add a column when it does not exist

To dynamically create a Stored Procedure is not very easy. Often it results in error messages such as "Unknown Token" or "Parse Error".

There are a few points of attention. The use of delimiters such as ', || and " is confusing. It is best to test this out, using IBExpert or a similar program. The use of " is not recommended; and in Delphi strings, you have to use double quotes (2 x ').

Another confusing point is the use of 'end of command' delimiters, such as ;. In this example, look at END;. This is different from what works in IBExpert, which does not require a ;. And in the end, you get DSQL code in your database that does not have a ; after END.

A last confusing point is that you have to set IBSQL.ParamCheck := FALSE;. Although parameters are used, they are internal SQL, not Delphi's to check.

procedure  TCDU.AddStoredProc;
begin
    IBSQL.ParamCheck := FALSE;
    IBSQL.SQL.Clear;
    with IBSQL.SQL do begin
       Add('CREATE OR ALTER PROCEDURE ADD_COLUMN ( ');
       Add('tab_name varchar(31), col_name varchar(31), ');
       Add('data_type varchar(100)) as ');
       Add('BEGIN ');
       Add('if (not exists( ');
       Add('select 1 from RDB$RELATION_FIELDS rf ');
       Add('where rf.RDB$RELATION_NAME = :tab_name and rf.RDB$FIELD_NAME = :col_name)) ');
       Add('then ');
       Add('execute statement ''ALTER TABLE ''||:tab_name||
           '' ADD ''||:col_name||'' ''||:data_type; ');
       Add('END;');
    end;
    IBTransaction1.Active := TRUE;
    IBSQL.Prepare;
    IBSQL.ExecQuery;
    IBTransaction1.Commit;
end