Friday, March 07, 2008
Butterflies and Stored Procedures
The Monarch caterpillar eats the poisonous milkweed plant so as an adult the butterfly is quite toxic to birds. A young bird will eat the butterfly and be so repulsed by the taste will not try to eat another in its lifetime. Some other species of butterflys mimic the color of the Monarch even though they are not poisonous, but quite tasty to birds. But a young bird after tasting the dreadful Monarch will not eat its counterfeit.
I'm like that with stored procedures. In one of my early projects stored procedures left a bad taste in my mouth. Stored procedures were cumbersome to code, difficult to synchronize across multiple databases and they always seemed to be out of sync with version control. Looking back all those issues could have be averted with better build management, but stored procedures left a bad taste in my mouth.
Today, one of my programmers came up with a good case for a stored procedure. He is working on one program that interfaces with a database, but many other programs are constantly changing the database. Currently they all issue a series of sql commands to affect a change in the database, but if one of the programs fails to get all the sql commands for a change correct, the database will be in an unsteady state.
Better to use a stored procedure to ensure consistency across the applications.
Maybe that first butterfly was a fluke.