1

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

rlb.usa
  • 14,942
  • 16
  • 80
  • 128
Mike D
  • 159
  • 1
  • 1
  • 13

3 Answers3

4

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
  • 47,048
  • 33
  • 131
  • 198
  • 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 Jun 16 '10 at 18:58
  • 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 Jun 16 '10 at 19:10
  • +1. This makes the absolute most sense. I haven't used labels in so long I forgot that was even an option. – NotMe Jun 16 '10 at 20:15
  • After searching all the code and procedures, there were only 3 instances, and they all seemed to be copied code (same routine name, same syntax...) Tried it with & without the colon, and on SQL2000 & SQL2005 with the same results all around.. (except 2000 allows the colon & doesn't seem to care it's there..) Again, Thanks All! – Mike D Jun 16 '10 at 20:41
  • Raj, I have the same code here and it's not being used as a label. It's an inline sql string in vb6. But as Mike stated, it looks like it can be removed. I'll verify a bit more first before doing so. – Brady Moritz Apr 08 '11 at 20:38
0

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

NotMe
  • 87,343
  • 27
  • 171
  • 245
  • 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 Jun 16 '10 at 18:49
  • 2
    @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. – NotMe Jun 16 '10 at 18:52
  • 1
    Probably what I'll end up doing anyway. Or probably step thru the code to where the call is made, copy the SQL generated by the code & try it in Enterprise Manager to see what happens – Mike D Jun 16 '10 at 18:53
0

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
  • 14,942
  • 16
  • 80
  • 128
  • Yeah I know how an else functions... The code (I inherited. In other words I didn;t write it, I maintain it...) Of course if I take out the ELSE it'll function different. My question is about the COLON. Why is it there & what is it's purpose? – Mike D Jun 16 '10 at 18:52
  • His problem isn't with ELSE it's with ELSE: Note the colon which causes the sql issue in later sql versions. – NotMe Jun 16 '10 at 18:52
  • Oh, in that case I agree with @Chris_Lively , it's a syntax, not semantics issue. – rlb.usa Jun 16 '10 at 18:54
  • 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 Jun 16 '10 at 18:56