How do I accurately handle a batch separator for SQL from C#

Question

For Data Explorer I would like to add support for a Batch separator.

So for example if users type in:

 
select 'GO' go select 1 as go 
Go 
select 100

I would like to return the three result sets.

Its clear that I need some sort of parser here, my hope is that this is a solved problem and I can just plug it in. (writing a full T-SQL parser is not something I would like to do)

What component / demo code could achieve splitting this batch into its 3 parts?

Answer 1

I'm not aware of an existing solution to this (though I agree that there probably is one out there). I just want to point out that you probably don't need to write a full T-SQL parser: all you really need to find is the word "go" outside of quotes. That is, look for <word boundary>GO<word boundary> and keep track of opening and closing quotes along the way. If you find a match and it's not after an opening quote (before its matching closing one) then it's the batch separator. It should be fairly easy to do this without writing anything that you'd call a proper "parser".

Answer 2

In your case above, can't you just split on newlines, test each line if it begins with the word "go", then split the script on that?

After re-reading this a couple of times, this is a really ugly problem. Looking at the first line in your script, there are actually no command delimiters (semicolons or newlines). I don't think you have much of a choice but to actually parse the whole thing.

But, somewhere along the line this has to get parsed anyway, right? Perhaps there's something you can do inside or using the existing parser for this. Depending on how much access you have to it, you could:

• Change the code for the existing parser to understand the "go" command to execute and return what it has, then run again.

• Take a copy of the existing parsing code, adapt it to understand the "go" command, strip out the interpreter part, then just use it to split blocks and feed to the real parser?

Answer 3

It's not often that I say this, but this is a case where I'd definitely advocate bending the user input to conform to computer rules rather than trying to solve the problem of having a computer understand massively varied user input

Impose a simple rule of: The word "go" must appear on its own line in order to be interpreted as a command to proceed

If your users can't adhere to a rule like that, should they really be engaged in the far more complex task of writing SQL queries?