Multiple statements in same script not working

[marcelovital]

When running a sql script with multiple statements, execution stops at the first one, or the second one is "not aware" of the first one.

Quick example:
DECLARE @abc VARCHAR(10) = 'test'; SELECT @abc;
returns:
Must declare the scalar variable "@abc".

It does also "misbehave" in the interface, with the connection information showing between lines.

If we add several blank lines to the start, and the example script mentioned above is in line 5, for instance, it stops throwing the error, but instead simply returns:
Success, 1 row affected

[mikeburgh]

This issue came up recently the other way (#802)… This is SQL Server right?

The problem is SQL Server does not treat ; as statement boundaries, it uses GO.. so it does not care about ; but every other dialect does care about ;

So I had changed it to match SQL Server and no care about ; but that caused issues for people where the expected them to work (since they do in every other engine).

I may need to just make this a setting so people can set SQL Server to run how they prefer..

In the meantime, if you drop the ; it will correctly see it as one statement block and execute how you are expecting..

[marcelovital]

This issue came up recently the other way (#802)… This is SQL Server right?

Correct. More especifically, Azure SQL.

The problem is SQL Server does not treat ; as statement boundaries, it uses GO.. so it does not care about ; but every other dialect does care about ;
So I had changed it to match SQL Server and no care about ; but that caused issues for people where the expected them to work (since they do in every other engine).

Got it, I didn't know about that. Indeed other engines don't have that issue, but the ";" is not simply ignored, right? Like the other dialects you mentioned, many times it is needed between statements.

Having a setting would be great too.

Thanks for the quick response, and let me know if I can assist, at least with testing.

[filipe-ig]

semicolon is not completely ignored in sql server but its quite low the use cases where its mandatory, the only one i remember is that you must have a semicolon if you start writing a CTE after another statement so that:

select * from a

with b_cte as (select * from b)
select * from b_cte

would throw an error

but

select * from a;

with b_cte as (select * from b) 
select * from b_cte

or even

select * from a

; with b_cte as (select * from b) 
select * from b_cte

would work fine

[mikeburgh]

Yes, that's a good point.. I actually decided against the setting as it just felt like a hassle for users to have to think about it.. and it would have broken depending on what you set it to with those WITH statements.

I made the parser smarter, so it will conditionally handle the semicolons based on the context.. so after regular statement like insert/select will be treated as a statement separator.. but in code blocks like DECLARE/SET etc it will now be ignored.

I am sure there could be a few edge cases I missed, so if you find any please let me know.

These changes have been released in version 1.17.35