CASE Statements in T-SQL

atcs2152 · April 27th, 2006, 02:36 PM

Is there a way to make a CASE statement work when it is not inside a SELECT statement? This is bugging me to no end. I know the CASE statement syntax and I have used one inside a SELECT as numerous examples show, but I can't get one to work outside a SELECT statement. For example, I want to do something like the following:

Code:

CREATE PROCEDURE TEST @SortOrder tinyint AS

CASE @SortOrder
    WHEN 1 THEN
        BEGIN
            SELECT szLastName, szFirstName
            FROM tblEmployees
            ORDER BY szLastName
        END
    WHEN 2 THEN
        BEGIN
            SELECT szLastName, szFirstName
            FROM tblEmployees
            ORDER BY szFirstName
        END
END
GO

This always generates a "Incorrect syntax near the keyword 'CASE'" error. I could do the same thing sucessfully with an IF/ELSE statement but that obviously isn't as efficient when there are more than two possibilities. I have tried to do this with both the Simply Case statement format as well as the Searched Case format.

I understand there are other methods to do dynamic sorting. I'm just using this as an example.

Jeff Mason · April 27th, 2006, 02:58 PM

In T/SQL, CASE is an expression, not an executable control-of-flow statement.

As an expression, it returns a value. This could be a column value which you could use in an ORDER BY clause, as:

Code:

SELECT szLastName, szFirstName
  FROM tblEmployees
 ORDER BY CASE @SortOrder
            WHEN 1 THEN szLastName
            ELSE szFirstName END;

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

atcs2152 · April 28th, 2006, 09:57 AM

OK, thanks.

I sure don't understand the reasoning behind why T-SQL is the way it is. I mean, why not design it so that I can use a CASE statement as a control of flow statement.

Any why didn't Microsoft make it so that we could simply pass a sort order as a varchar parameter and then do something like...

Code:

SELECT LastName, FirstName
FROM Employees
ORDER BY @SortOrder

Jeff Mason · April 28th, 2006, 10:53 AM

Well, don't blame T-SQL for the way CASE is defined. The ANSI SQL-92 standard defines CASE to be an expression. That is, it returns a value, so it can be used anywhere anytime in any statement where a value is required.

This is very handy when operating on sets of data, since the expression is evaluated (conceptually) on all elements of the set all at once at the same time.

Control-of-flow statements, on the other hand, are not part of the SQL language. They are a part of, in SQL Server's case, the language called T-SQL which accommodates the execution of SQL statements, along with other operations not part of the SQL language per se. If you want to influence which SQL statements are executed, you need to use T-SQL constructs like IF-ELSE for that.

As to your comment about ORDER BY, Microsoft, or any other vendor, cannot simply change the way SQL behaves. ORDER BY is a part of the language and its specification states that it must refer only to columns in the resultset. Some vendors (including MS) have extended this to allow ORDER BY on columns of the related tables in the query not in the resultset, but you'll see artifacts of the original restriction in things like UNION queries, where the ORDER BY clause must specify columns in the resultset only. Because of this, purists would argue that this sort of extension offends their sense of, er, purity. :)

I don't actually know, but I can guess that specifying the ORDER BY columns in a parameter or other variable would mean that the optimizer could not construct a full query plan until run-time since it couldn't know which columns were to be used for sorting until then. This would mean that the pre-compile of a plan to include indexes which might significantly help the sort operation could not be done.

Or something. ;)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com