SQL CASE Statement Versus Conditional Statements In Programming Language

Question

I'm going through some old stored procedures at work and constantly come across

CASE MyColumn WHEN 'Y' THEN 'Yes' WHEN 'N' THEN 'No' ELSE 'Unknown' END

It gets even worse when this is tied not to a word, but instead a colour.

CASE MyColumn WHEN 'Y' THEN 'style="background-color:pink"' ELSE '' END

The reason this was done was for older ASP 1 pages where everything had to be done inline, but as it's such a large system it's impossible to keep up with updating all the pages.

Can anyone give any valid evidence that using a SQL query for conditional statements surpasses that in other languages such as C# or Java? Is this good practice for speed? Should the plain value be returned and the presentation layer decide what should be done?

Answer 1

I would be concerned putting this kind of logic in SQL statements. What happens if your database engine changes? Will you have to update every SQL statement to Oracle SQL? What if the repository itself changes, when you move to a message bus, XML files, or web service call...

It looks like you're storing display information. In which case, it's part of the data model. Let the controller (in a typical MVC pattern) perform the conditional logic. The presentation layer doesn't need to know what happened and the repository can be happy just holding data.

Answer 2

When speed is of the essence, the SQL case statements might even be the fastest (I'll run a test) but for maintainability, returning the plain values to the presentation layer (or some business layer thingy) is the best option.

[update] ran some quick and dirty tests (code below) and found the C# code variant slightly faster than the SQL case variant. Conclusion: returning the 'raw' data and manipulating it in the presentation layer is both quicker and more maintainable.

-Edoode

I retrieved 196288 rows with queries below.

StringBuilder result = new StringBuilder();
using (SqlConnection conn = new SqlConnection(Settings.Default.Conn))
{                
    conn.Open();
    string cmd = "select [state], case [state] when 'ca' then 'california' else [state] end from member";
    SqlCommand command = new SqlCommand(cmd, conn);
    using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
    {
        while (reader.Read())
        {                        
            result.AppendLine(reader.GetString(1));
        }
    }
}

C# variant:

StringBuilder result = new StringBuilder();
using (SqlConnection conn = new SqlConnection(Settings.Default.Conn))
{

    conn.Open();
    string cmd = "select [state] from member";
    SqlCommand command = new SqlCommand(cmd, conn);
    using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
    {
        while (reader.Read())
        {
            result.AppendLine(reader.GetString(0) == "ca" ? "california" : reader.GetString(0));
        }
    }

}

Answer 3

Can anyone give any valid evidence that using a SQL query for conditional statements surpasses that in other languages such as C# or Java? Is this good practice for speed? Should the plain value be returned and the presentation layer decide what should be done?

Sometimes (sometimes) it just helps to avoid extra coding in the presentation layer.

As a rule, if both database and presentation are developed by one person, it doesn't matter. Problems begin when the work becames shared. In this case you, obviously, need a well stated contract on what the database emits and what ASP accepts.

Sure, CSS belongs to presentation layer's domain and it should be parsed by ASP rather than hardcoded in SQL.

Answer 4

That is old style code, I don't believe people code like this anymore (we use CSS now)
Your are probably looking at some soon to be legacy stuff which will be rewritten or abandoned

Answer 5

In terms of SQL Performance, if you are only returning a few rows (or one by the looks of it), this has minimal impact as the WHERE part of the statement is evaluated before the case (The case is only performed on rows which match the where).

From a best practises point of view, display information should not really be determined in SQL. Perhaps returning a theme type, but certainly not discreet style information.

Answer 6

Agree 100% on refactoring to put display decisions up to the presentation layer (or as near as possible).

CASE ... END in SQL still can have its uses, though, for example, say I want to calculate the percentage of the total value of all orders that is for domestic customers. I think I'd be happy with

SELECT
  SUM(CASE domestic WHEN 'Y' THEN order_value ELSE 0 END) / SUM(order_value) AS pctg
FROM orders

as a query. Unless someone knows better, of course.