It isn’t that such objects are to be completely avoided, it is that they are ONLY to be used when you need the special qualities. Ola Hallengren’s backup solution creates a
dbo.sp_BackupServerprocedure so you can run the backup command from any database.
But if you don’t need the special properties of the
sp_procedure, they are bad for the reasons Aaron stated, the reason I stumbled upon today being just a special subset. In this case
CREATE OR ALTERbehaves differently than
CREATEin a way that was really confusing to me as I was working on a piece of code today.
Read on for Louis’s tale of woe and confusion, but mostly confusion.