views:

53

answers:

3

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 in advance...

A: 

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

Chris Lively
Because ELSE is used in SQL case statements to catch whatever hasn't been matched - potentially a big part of the SQL statement.
rlb.usa
@rlb.usa: I know what ELSE does. However, if his stated option is that he wants to remove it but not sure the impact.. then by definition he SHOULD remove it and test the impact. That's the way to learn.
Chris Lively
Mike D
A: 

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.

rlb.usa
Mike D
His problem isn't with ELSE it's with ELSE: Note the colon which causes the sql issue in later sql versions.
Chris Lively
Oh, in that case I agree with @Chris_Lively , it's a syntax, not semantics issue.
rlb.usa
Right, try it and see what happens without the colon is about my only option. I too could find no documentation for the colon...
Mike D
+4  A: 

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.

Raj More
Could have been that too. I hope not though... NOw I need to look for a GOTO ELSE? Along with the terrible indention, now we have reserved words for tags... I give up..
Mike D
Or they may have inadvertently used ELSE as a label and just didn't realize that the Else was not working properly. But yes, I'd search for GOTO Else statements.
HLGEM
+1. This makes the absolute most sense. I haven't used labels in so long I forgot that was even an option.
Chris Lively
Mike D