Smoke Test or Get Burned

I’m guessing that many developers, and novice to intermediate DBAs, are under the impression that they are golden if no error is returned when they press F5 (or click Execute) to create a stored procedure. In their defense, the rules are moderately complicated.

For example, the fact that executing the following code against AdventureWorks2008 leads to an error, might lead you to believe that SQL Server will prevent you from making stupid mistakes:

This message clearly shows that SQL Server has got my back and says that Twitter wasn’t cool enough for the minds at the legendary AdventureWorks to include it in the Person table. Say it ain’t so! If a Person doesn’t have a Twitter handle, how are we going to be able to communicate with them?!

I know, maybe it was in the Person.Twitter table?

BAM, we knew the peeps over at AdventureWorks wouldn’t let us down.

Now, let’s run that sproc and check out the list of TwitterHandles they had back in the day:

Wait a minute, I just created that sproc moments ago, and SQL Server was perfectly happy then. What’s the deal?

Deferred Name Resolution

According to BOL, when a stored procedure is created, the statements in the procedure are parsed for syntactical accuracy. If a syntactical error is encountered in the procedure definition, an error is returned and the stored procedure is not created. Otherwise, the text is stored in sys.sql_modules. There is also a NOTE explaining deferred name resolution, and the results shown above. It goes on to explain that you won’t see the error until the first time the procedure is executed, which bring us back to Smoke Test or Get Burned. If you know of a low effort tool that will rip through my sprocs and smokem all, please tell me about it in the comments. Otherwise, this article is going to be foreshadowing…