Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 27th, 2006, 02:36 PM
Authorized User
 
Join Date: Dec 2005
Location: , , .
Posts: 21
Thanks: 0
Thanked 1 Time in 1 Post
Default CASE Statements in T-SQL

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.


Reply With Quote
  #2 (permalink)  
Old April 27th, 2006, 02:58 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #3 (permalink)  
Old April 28th, 2006, 09:57 AM
Authorized User
 
Join Date: Dec 2005
Location: , , .
Posts: 21
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #4 (permalink)  
Old April 28th, 2006, 10:53 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Switch Case statements ermy78 Access VBA 3 October 20th, 2006 08:58 AM
Case Select Statements & Updating the Database jackiew General .NET 1 April 11th, 2006 11:47 AM
Case Statements cfriedberg SQL Server 2000 1 September 7th, 2005 08:46 PM
SQL Statements marmer Classic ASP Basics 3 November 13th, 2003 01:42 AM
Case Statements in Access dbegg Access 2 September 17th, 2003 04:30 PM



All times are GMT -4. The time now is 07:47 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.