CODEWITHSUNDEEP
t-sqlazure-sql-databasedbeaver
bugwheels94
bugwheels94

Reputation: 31940

Error using semicolon in Azure SQL Server

I am using Microsoft SQL Azure (RTM) - 12.0.2000.8 Nov 29 2017 09:37:51 Copyright (C) 2017 Microsoft Corporation with compatibilty_level=120 and when I am running this query

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: Error

Upvotes: 4

Views: 680

Answers (2)

Alberto Morillo
Alberto Morillo

Reputation: 15698

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.

Upvotes: 1

Randy Minder
Randy Minder

Reputation: 48502

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.

Upvotes: 1

Related Questions