A discussion of where logic should go and why SQL might be the best place.

SQL is a powerful language and is very good at what it was made for – querying sets of data. C#, Java, and other languages are also powerful – more powerful than SQL in many ways. For looping and doing procedural tasks, C# can process records hundreds of times faster than a SQL cursor, and do it in fewer lines of logic. SQL simply wasn’t made to do this type of processing. Instead, SQL was made for set-based processing where tables and result-sets are used and merged together based on conditions. In a future post I’ll talk about cursors, why they’re painfully slow, and what to do about them.

Similarly, SQL is as far from the User Interface as you can get. As such, many UI-related tasks are generally not appropriate in the SQL logic. For example, if names should appear on a web data grid with the format ’[first name],[last name][middle initial]‘, one option is to return the data from the SQL statement in this format. However, an arguably more appropriate method is to simply return the name parts and have logic in the user interface format the full name in the desired format.

One example where it is uncertain whether the logic should be in SQL was raised on the SQLTeam website here. In this scenario, the primary value [name] being displayed covers multiple rows in the UI, and some of the data fields should only show up for the first instance of the name. At first glance, this type of data hiding seems more like a UI-related issue than anything that should be in the SQL query. But, as is evident in the thread, what if the UI is locked down?

In my next post, I’ll discuss a SQL question and answer [that I provided] from SQLTeam in some depth. For this question, it is arguable this logic should be in the business logic layer. Another forum poster suggested as much, and in a way I agree. However, the decision of where to put logic isn’t always clear cut.

Some questions to ponder:

1) What if, as was the case in the forum thread, the UI or business logic layer is locked down and the only way to change the display is to change the data loaded in?

2) What if instead of 1 calling website, this report needs to display on 2-3 web applications, be made available via a web service, be pulled by a console application, etc. While in theory a common business logic component could be developed that all of these could reference, another [likely easier] option it to simply do the centralization at the database level in a stored procedure.

3) What if the result needs to be referenced by some other piece of SQL logic? Maybe it can be transformed into a view.

4) What if the result is destined for an Excel file being whipped up quickly for a user asking for an ad-hoc report? What if they ask for it again with a slight logic modification a few minutes after you finished modifying the Excel file formatting? Wouldn’t it be easier just having the logic in the SQL in this case?

5) What if the developer tasked to writing the logic is simply more adept at coming up with a SQL-based solution for the proposed need?

For me, if something can be done in a set-based manner, I’ll attempt to think of a clean SQL-based way to get the job done. If at all possible, anything that can be done in a set-based manner goes into SQL logic [and anything that requires procedural logic goes into code].