How to create conditional DROP statements in SQL scripts

Todd Brasseur wrote:

We have an application with clients that each have their own database. Each of these databases are supposed to have the same metadata.

Over the years, things happen, and the metadata ends up different. An example that happened today was a UDF was supposed to have been dropped in the database and it wasn't.

When clients install a new release of our application, they get the new application and we also run 'scripts' to update their databases if need be.

I would like to add a script that does a "DROP EXTERNAL FUNCTION SQRT".

The problem I have is that some of the databases that will run this script already have the UDF dropped and so the script errors.

Is there a way to run statements like DROP INDEX, DROP EXTERNAL FUNCTION, etc. in a script without generating an error of the object isn't found?

Sean Leyne answers:

This should do:

EXECUTE BLOCK
AS
BEGIN
  IF (EXISTS (SELECT 1 FROM RDB$Functions WHERE RDB$FunctionName = 'SQRT') ) THEN
    EXECUTE STATEMENT 'DROP EXTERNAL FUNCTION SQRT';
END