This is another story from the app with the sprocs, an app where a lot of the business logic (BL) is inside a large number of stored procedures (sprocs). These are written by the domain expert himself. As the BL changes the app changes as well, which leads to change of the table structures (the database schema) and the sql code in the sprocs. Whether sprocs are a good or bad place for BL is a discussion I do not want to repeat. Right now the BL is just over there; moving it is too big a step. But recently we bumped into something which I will name "sloppy SQL" and is imho another reason for getting it out of the DB and into an environment which forces stricter code and a way to get away from the sql itself.
What happened ? Some of the tables got a different structure, we dropped a column here and added a column there. Table columns have a determined order, it is the order in which they appear in the designer. When you issue a sloppy SQL statement like
SELECT * FROM Customers
this column order determines in which order the values in the result rows are. When you issue a neater statement, like
SELECT Name, Address, ZIP, Town FROM Customers
the statement determines the order.
Things get worse when it comes to inserting rows. Take this statement to insert a new customer
INSERT INTO Customer (Name, Address, Zip, Town)
VALUES ('MyCustomer', 'MyStreet', 'MyZip', 'MyTown')
Here the mapping between columns and values to insert is clear. But this is also a valid sql statement:
INSERT INTO Customer
VALUES ('MyCustomer', 'MyStreet', 'MyZip', 'MyTown')
Here the values are mapped onto the columns according to the column order. This statement will execute provided you pass a value for every column.
Now what if the order of the columns is different from the assumed order in the values ? In the worst case you will write the wrong data in the wrong columns, in the best case you will hit a sql exception because of failing typecast or the like.
The sprocs in our app do contain a lot of these sloppy SQL statements. The guy who writes them has an excellent knowledge of the domain, is not a bad coder at all, just a lazy typer. What can you do ? We did not notice this problem until the app was rolled out to a testing server. To synchronize the database we are using Red Gate's SQL compare, which is a marvel of a tool I introduced before. It generates a script to alter the database schema. Modifying the structure of a table is on itself a matter of dropping some columns and appending the new ones. As a result all the new columns always come last in the column order. Which is different from the order assumed in the sprocs. So running the sprocs led, thank goodness, to exceptions.
Red Gate wouldn't be Red Gate if it didn't have an option Force Column order.
This is not set by default. The option generates a different script which
- Copies the data to a temporary table
- Drops the table
- Recreates the table with the columns exactly in the desired order
- Repopulates the table from the temporary one
And now the sprocs will work as intended.
There are a lot of other options to set in Sql Compare, the list is worth a study. Notice the two sets of settings, My Defaults and Red Gate Defaults. You can trust Red Gate to be the experts but not all of their defaults are best for your (or my) scenarios.
For our app it would be a better alternative to have all BL inside C# code where we have things like strong typing and unit tests to guard it. But that would take a lot of work, including making me a true domain expert. I am learning on there but still have a long way to go. For now it's Red Gate to the rescue.