SQL Server Query with "ELSE:"

I have various VB6 projects I'm maintaining with some of the queries being passed to the server having "ELSE:" with the colon used in case statements.

I'm wondering can someone tell me what the **ll the colon is used for? It causes errors in SQL2005 and greater, but SQL2000 works with no complaints.

I'd like to just remove it from the code & re-compile, but I'm afraid it'll break 10 other things in the application..

Thanks 开发者_如何学Pythonin advance...

Here is the deal.. somebody used the ELSE keyword as a LABEL in your code.

A word in TSQL followed by a colon is a Label. Here is a sample:

DECLARE @Count int
SET @Count = 0

ONE_MORE_TIME:
IF @Count <> 33
    PRINT ‘Hello World’
    SET @Count = @Count + 1
END

IF @Count <> GOTO ONE_MORE_TIME

In your case, the label might be "ELSE"

DECLARE @Count int
SET @Count = 0

ELSE:
IF @Count < 33
    PRINT ‘Hello World’
    SET @Count = @Count + 1
END

IF @Count < 33 GOTO ELSE

I have a feeling that this code is going to be badly indented as well. I'd almost be willing to put some money down on that.

why not remove it, recompile, and test the application to see what impact it may have?

Hmm, it causes errors in SQL2005?

In SQL server, ELSE is used in case statements to catch anything that hasn't been caught yet (it pertains to things that have "fallen through" all of the other cases). For example, suppose we'd like to list some items from a price database and categorize them with a custom 'Budget' text column, so for each item we'll look at it's price and set what it's 'Budget' value should be:

SELECT title, price,
        Budget = CASE price
         WHEN price > 20.00 THEN 'Expensive'
          WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
          WHEN price < 10.00 THEN 'Inexpensive'
          ELSE 'Unknown'
        END,
FROM titles

The ELSE here catches everything that didn't really fall under "Expensive" or "Moderate" or "Inexpensive". Removing these ELSEs here will definitely mess your queries up.