0

I have problem with conditional SQL script in SQL Server 2008.

When I run each command as separate everything is OK and I successfully update my DB

But when I split everything into one command I get errors:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ';'.

Msg 207, Level 16, State 1, Line 1
Invalid column name 'sAdvertTypeCode'.

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'end'.

So I tried without semicolon ';'

And error was:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.

Msg 207, Level 16, State 1, Line 1
Invalid column name 'sAdvertTypeCode'.

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'end'.

My script :

IF COL_LENGTH('TechnicalDialogue', 'sAdvertTypeCode') IS NULL
begin
    ALTER TABLE TechnicalDialogue ADD sAdvertTypeCode varchar(40) ;
    go
    UPDATE TechnicalDialogue SET sAdvertTypeCode = 'advert.type.broadcast' ;
    go
    Select * FROM TechnicalDialogue;
end

I don't have a lot experience with SQL Server so I'm a little bit confused.

Community
  • 1
  • 1
Mazeryt
  • 855
  • 1
  • 18
  • 37
  • 2
    Remove the `GO`, I don't think you can use it within a block. – Mark Rotteveel Aug 13 '14 at 08:12
  • 6
    GO isn't a SQL statement. It's a [command used in the client to execute batches of SQL](http://stackoverflow.com/a/2668549/300836), so you can't have it in the middle of a BEGIN/END. `GO` will send everything since the start of the script or the last GO to the server for execution, so the server sees a `begin` but no `end`. – Matt Gibson Aug 13 '14 at 08:13
  • Thanks it help me I didn't notice that :) – Mazeryt Aug 13 '14 at 08:15
  • 1
    @MattGibson: Please re-post your comment as an answer, because that's what it is. – stakx - no longer contributing Aug 13 '14 at 08:27
  • @stakx If that is an answer, then it [surely](http://stackoverflow.com/questions/1180279/when-do-i-need-to-use-begin-end-blocks-and-the-go-keyword-in-sql-server) [must](http://stackoverflow.com/questions/6376866/tsql-how-to-use-go-inside-of-a-begin-end-block) be a duplicate. I'd have imagined there was more to answer here, as I wouldn't expect it to work if you just removed the GO. On the other hand, [that looks like a duplicate, too](http://stackoverflow.com/questions/3397423/how-can-i-update-a-newly-added-column-in-the-same-batch-statement) – Matt Gibson Aug 13 '14 at 08:44
  • @MattGibson: Your comment gives an explanation why it doesn't work, and why removing the `;` doesn't help, either. The question didn't explicitly ask for the corrected code; so FWIW I would upvote your answer. (And if the question is in fact a duplicate, then it is a duplicate, no matter whether you posted an answer, or whether you posted an answer as a comment.) – stakx - no longer contributing Aug 13 '14 at 08:47

1 Answers1

3

GO isn't a SQL statement. It's a command used in the client to execute batches of SQL, so you can't have it in the middle of a BEGIN/END. GO will send everything since the start of the script or the last GO to the server for execution, so the server sees a begin but no end. – Matt Gibson 26 mins ago

Community
  • 1
  • 1
Matt Gibson
  • 37,886
  • 9
  • 99
  • 128