Thursday, March 3, 2011

Weird SQL Server Management studio behavior

I am currently using SQL Server Management Studio (Ver 9.00.3042.00) and click the "New Query" and wrote the following code:

Select 
    colA,
    colB,
    colC
    colD
From
    table1

When I click the parse (checkbox) button to validate the SQL statement, it says "Command(s) completed successfully". Isn't the parse button suppose to catch these simple errors...

Has anyone experienced this type of behavior from Management Studio? This just started happening this week....

From stackoverflow
  • That's because that is valid SQL. You're effectively aliasing colC with the name colD. It's the same as typing:

    colC as colD
    

    Edit: For what it's worth, this is one of the reasons why people will argue that you should put the commas at the beginning of the line in cases like this. It's a lot easier to spot gotchas like this when the code is formatted as such:

    Select 
        colA
        , colB
        , colC
        colD
    From
        table1
    
    Michael Kniskern : When I try to parse a select statement that was missing a comma in a stored procedure is would throw a 'Incorrect syntax near colC'
    Sören Kuklau : That's because stored procedure parameters don't support this kind of aliasing, since they're not columns. SELECT columns do, however.
    Michael Kniskern : @Soren - I just tried the same behaviour in a stored procedure template and it passed the parse check.
  • After a little playing around, it throws the "Incorrect syntax near" on the following code snippets

    Select     
        colA,    
        colB    
        colC    
        colD
    From    
        table1
    
    Select     
        colA,    
        colB,    
        colC,    
        colD,
    From    
        table1
    
    John Rudy : As well it should -- in your original post, you created a column alias. In the top one here, that wouldn't play as you have two tokens after colB (colC, colD). In your bottom example, there is a genuine syntax error in that there is no valid select token after "colD," ... @Jeromy Irvine is right. :)

0 comments:

Post a Comment