I learned to program using procedural languages, in which any given procedure does exactly one thing; and, if you need to do three things, you write three procedures and invoke them successively.

Almost everyone programs in OOP now, using objects and their methods. SQL programmers cannot yet write stored procedures and functions using these concepts (but you will be able to in Yukon). However, OOP and procedural programming aren't that far apart. In an OOP language, the same principle for procedural languages applies: each method should do exactly one thing; and, if it must perform subtasks, then each of these should be an atomic unit, which does exactly one thing.

Let's revisit the original question. Even if you could, why would you want to write a query that updates existing rows and also inserts new rows? Why not write two queries, the former performing the updates and the latter the inserts? What do you gain by staying up nights figuring out the SQL that can do this? And, more importantly, what do you lose?

The answer is transparency. Any such query, assuming that it can be written, is bound to be almost incomprehensible to the average new hire; whereas a pair of queries that are each devoted to one task is blatantly clear.

This perspective applies to stored procedures, updateable views, and dynamic SQL queries. When I see code that performs several different actions on several different tables, it reminds me of the Saturday Night Live skit about the product that is both a dessert topping and a floor wax. If you really need a stored procedure like that, write three: DessertTopping, FloorWax, and Both—the latter containing calls to the former two. Then, simply update these rows and insert those rows. The overhead in calling a stored procedure is the least of your worries.

I call these procedures Atomic and Molecular. Atomic procedures do exactly one thing, while Molecular procedures don't do any work on their own; instead, they invoke several Atomic procedures in the framework of a transaction, or unit of work.

Last updated 5 Sep 2024