Differences between @@recreate procedure@@ and @@create or alter procedure@@

<< Debugger Collect Statistics example | IBExpert | Sample procedure: determining age >>

Differences between recreate procedure and create or alter procedure

By Jiri Cincura: http://blog.cincura.net

I was recently in a talk related to Firebird and I found, that people are not aware of these two constructs. Either they don't know both or don't know they differ. These statements are doing similar stuff, but the evil is in the details.

So what's the big deal? Both are kind of "updating" the procedure (or other object types). But the first one will first drop the procedure and then create it back again (yes, dependencies may break that). On the other hand, the other is creating the procedure if it doesn't exist yet, or altering it otherwise. Dependencies aside, what else might be attached to a procedure? Yes, it's access rights (GRANTs). The former one will not keep these. You're responsible for granting access to it again. The other one will, it's just an alteration of the procedure definition.

If not used carefully, you can easily break the database. Either one isn't correct in all cases. Always use what's appropriate for your scenario.

See also:
SELECT
DDL - Data Definition Language
Stored procedure and trigger language
Writing stored procedures and triggers
Stored procedure

back to top of page
<< Debugger Collect Statistics example | IBExpert | Sample procedure: determining age >>