Error using semicolon in Azure SQL Server

Question

ALTER PROCEDURE SPCreateSession3
@a int,
@b int
AS
select count(*) as a from events where IDevent > @a;
select count(*) as b from sessions where IDsession > @b;
GO
exec SPCreateSession3 1, 1

It is giving me error Must declare the scalar variable @b. Without semicolon, it is working fine. It looks like that ; is caugin some sort of GO command. Then why many people suggested here, that ; can never cause any problems. I need semicolon as I am using MERGE command in reality for which ; is necessary. Also, If I wrap the queries in BEGIN Block like

ALTER PROCEDURE SPCreateSession3
@a int,
@b int
AS
BEGIN
select count(*) as a from events where IDevent > @a;
select count(*) as b from sessions where IDsession > @b;
END
GO
exec SPCreateSession3 1, 1

Then, the sql server says incorrect syntax near ;. Again a problem because of semicolon. Am I missing something or semicolon is really to blame here? What are the workarounds around this when I need to use semicolon without getting any of these two errors?

Snapshot:

I have no EXP in azure-SQL... are there any service packs available for that? — TT., Dec 25 '17 at 06:48
@TT. No idea. but something is here https://support.microsoft.com/en-us/help/4041553/sql-server-service-packs-are-discontinued-starting-from-sql-server — bugwheels94, Dec 25 '17 at 06:49
I am not familiar with the editor you are using. If you have access to windows, you can install SSMS, connect to Azure and check if the code is working. If not, then we can be sure it is a Azure bug and you can even create a connect ticket about it. If it's working - then it will be the editor and some syntax check which it is performing before executing your query. — gotqn, Dec 25 '17 at 09:29
Please SQL Operations Studio. Download it from the following URL. https://github.com/Microsoft/sqlopsstudio — Alberto Morillo, Dec 25 '17 at 14:21
@AlbertoMorillo If you want, you can add that as an answer for future people otherwise I self answer because changing editor worked. — bugwheels94, Dec 25 '17 at 19:43

score 1 · Answer 1 · answered Dec 25 '17 at 20:42

1

This is not an Azure issue. A semi-colon in T-SQL ends a batch (scope). After the semi-colon, you are starting a new batch (scope) and any variables declared in the prior batch are no longer valid. In this respect, a semi-color acts the same as the GO statement; it ends a batch of statements.

answered Dec 25 '17 at 20:42

Randy Minder

47,200
49
204
358

Not exactly. The same thing is working in other editors like VS Code – bugwheels94 Dec 25 '17 at 20:55

score 1 · Accepted Answer · answered Dec 25 '17 at 23:53

As a workaround for this issue, please use Microsoft SQL Operations Studio as an alternative query editor for Linux and MAC. Download it from here. You won't have this issue with Operations Studio.

Users are currently demanding make SQL Server Management Studio a cross-platform tool as you can see here. For now you can use Microsoft SQL Operations Studio.

Error using semicolon in Azure SQL Server

2 Answers2

Linked

Related