Script: check if a stored procedure exists and if so drop?

[bmarks]

Is there a way, in a script, to check if a stored procedure already exists?

What I want to do is either create or alter a stored procedure. Create, if it does not already exists. Alter, if it does exists. So I'm thinking if the script can see if it exists then it can drop it. Then I can use create procedure and it will work for either case.

I want to do it this way as I'm creating scripts to be applied to various client databases and some clients already have versions of these stored procedures in their database and other clients don't.

Can this be done?
Thanks in advance.

 

[unclejimbob]

Can this be done?"
Not natively as InterBase does not allow you to mix DML and DDL statements.

Work arounds

1) Issue drop/create statement pairs as part of a script and ignore any
'procedure <xxx> not found' messages

Here is a code example:

-- Command line used to run the test.sql script to add/modify an SP named 'atemp'
isql -user SYSDBA -password masterkey c:\test\test.ib -input c:\temp\test.sql

-- Procedure stored in file test.sql (note the last 2
-- blank lines)
drop procedure atemp;
commit;
create procedure atemp
returns (i integer)
as
begin
select rdb$relation_id from rdb$database into :i;
suspend;
end
;
commit;


2) Make it a rule that your clients all have _exactly_ the same database structure

 

[bmarks]

unclejimbob,
Thanks for the response. I was afraid that would be the answer. I'll have to check if the ignore of the "procedure not found" message would work for us.

In the sample you state (note the last 2 blank lines) - what is the reason for the 2 blank lines?

#2 is not an option for us as we clients on various versions of the software and on various versions of IB (I don't like that either, but it was established long before I started working with them). They do have a routine for adding procedures in a set of scripts that only get run once per client and then all changes are done with a different set that does alters only. However, I'm trying to setup a new system for a new portion of the product and wanted to avoid needing two sets of scripts.

 

[unclejimbob]

In the sample you state (note the last 2 blank lines) - what is the reason for the 2 blank lines?"
Sure, in older versions of InterBase I always had an issue with the script not being read correctly unless I left a couple of CRLFs at the end of it, and other people have had a similar problem. I am pretty sure it doesn't happen any more - but old habits die hard. ;-)

 

[bmarks]

ujb, I had not heard of that before - but then again I've never worked on any versions older than 6. Good to know though in case I run into that issue sometime.

 

[bmarks]

ujb,
I tried that. However, the fact that an error is generated will cause us problems. Since these scripts will get bundled with many other scripts (and then the bundle sent to clients), any errors generated will get investigated multiple times and also seen by the client. We really need to be able to produce clean script bundles.

Don't know, but could there be a way to trap and remove the error?

 

[unclejimbob]

There is no way I know of within a script to trap and remove an error generated by a DDL statement.

I guess you would have to start looking at other options, such as:

1) removing the procedure indirectly through stuff like this:

DELETE FROM RDB$PROCEDURES WHERE RDB$PROCEDURE_NAME = 'ATEMP';
COMMIT;

...thus avoiding the issue of whether or not the procedure exists in the first place, you just dump it from the rd$procedures table and create it again - although of course if the SP has dependencies then you will have to dump the dependencies first and recreate them afterwards...

or
2) coding the schema changes within a small utility and then trap and handle the return SQLCODE for each and every potential occurence

FWIW our product only uses one version of InterBase and one schema - and we actually hold the IB licenses on behalf of all our clients.

We get buy in from all the user sites because it means they get a more cost-effective solution due to us not having to jump through hoops in order to maintain and support the product.

Happy days.

 

[bmarks]

The first option might have some potential. I'll have to look into that one further.

I wish we could use only one version of IB and one schema, but due to the history of the product and the amount of customization that is done for some clients (and allowing clients not to upgrade, but use older versions with patches) has led to this.

I'm hoping that we can start moving to a more standard approach as if we obtain more clients and continue to grow, it will become unmanageable.