tabs ↹ over ␣ ␣ ␣ spaces

by Jiří {x2} Činčura

Differences between “recreate procedure/…" and “create or alter procedure/…”

3 Apr 2012 1 mins Firebird, SQL

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 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 another is creating the procedure if it doesn’t exist yet or altering it otherwise. Dependencies aside, what else might be “attached” to procedure? Yes, it’s i.e. access rights (grants). The former one will not keep these. You’re responsible to granting access to it again. The other one will, it’s just alter of 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.

Profile Picture Jiří Činčura is an independent developer, .NET, C# and Firebird expert, focusing on data and business layers, language constructs, parallelism, databases and performance. He's Microsoft Most Valuable Professional and frequent speaker. You can read his articles, guides and tips and tricks at www.tabsoverspaces.com.