Try to execute a T-SQL script which contains several SQL batches separated by the GO keyword using .Net’s SqlCommand, and you will receive an error message
Incorrect syntax near ‘GO’.
What’s going on here? Why does it work from SSMS or sqlcmd, but not in SqlCommand?
The reason for this behavior is that ‘GO’ is not a statement of T-SQL, but rather
a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor
(MSDN). You think you might simply remove all occurrences of GO inside your statement, but that will not always work: Some statements require to be the first statement in a batch
‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch.
So how do you execute a set of SQL statements containing ‘GO’ in C#?
The “lazy” way is to simply split the SQL string at each occurrence of ‘GO’ using string.Split(), but this is really lazy.
Why? Because of the complexity of the language that the T-SQL shells are capable of processing:
- string literals ” and N”
- single line comments —
- multi-line comments /* */
- nested multi-line comments (!)
The characters ‘GO’ must not be recognized if they occur inside any of these constructs, and they need not be the first characters in a line – the line may also contain spaces or whitespace or comments (just give it a try in SSMS, it’s truly amazing).
Or, you write a parser using ANTLR, define the correct grammar to split at the relevant GO keywords, and execute the list of resulting SQL batches separately.😉