MSSQL: Declare variable and use it in select-statement cause a SQL-ERROR 137

[KingAlex1985]

Short description:

When I use "Microsoft SQL Management Studio" then the SQL-Script works without errors. When I use HeidiSQL (Version 9.5.0.5196 or earlier) then I get SQL-ERROR 137.

Steps to reproduce this issue

• Using a Microsoft SQL Database ("Microsoft SQL Server 2016 Express")
• Haveing a normal table which is called "ProductionOrder".
• Using SQL Statement:

DECLARE @poId BIGINT; SET @poId = 27; SELECT * FROM ProductionOrder po WHERE po.productionOrderId = @poId;

or like this (similar code)

DECLARE @poId BIGINT = 27; SELECT * FROM ProductionOrder po WHERE po.productionOrderId = @poId;

Then I get the following error message (in german): SQL Error (137): Die @poId-Skalarvariable muss deklariert werden.

See the 3 screenshot (Link goes to bilder-upload.eu): http://www.bilder-upload.eu/show.php?file=f1754b-1525860763.png http://www.bilder-upload.eu/show.php?file=9a268e-1525860788.png http://www.bilder-upload.eu/show.php?file=5777b4-1525860820.png

Current behavior

Getting SQL Error (137)

Expected behavior

Should execute select-statement without errors.

Possible solution

Environment

• HeidiSQL version: 9.5.0.5196
• Database system and version: Microsoft SQL Server 2016 Express: Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30
• Operating system: Windows 10

[ansgarbecker]

SQL Error (137): Die @poid-skalarvariable muss deklariert werden.

That's a server generated message. Are you sure that HeidiSQL is to blame here?

[KingAlex1985]

Hm, I'm not sure. And I didn't want to blame HeidiSQL. Wanted to report this Issue. Maybe I'm not the one who is using HeidiSQL in combination with MS SQL Server 2016.

So what is the problem than? Is it the communication between HeidiSQL and MS SQL Server 2016??

[ansgarbecker]

I don't know. I even don't know much about the MS SQL dialect. But as long as there is no problem with HeidiSQL itself, I cannot do anything here. Perhaps the DECLARE query is not complete? You will need to dive deeper into the SQL error, perhaps you find some solution on the net. And if it's really an issue in HeidiSQL, then just report back here to tell us what's going on here.

[KingAlex1985]

Ok, now I tested the same SQL Statement with "DBeaver" and "TablePlus". With this programs it works fine. The statement is not realy dificult. Only set a variable and use it with @xxx in the sql statement.

[steffchep]

Same Problem: It's a Microsoft SQL DB DECLARE @adminId UNIQUEIDENTIFIER; SELECT @adminId = unid from operators WHERE loginname = 'admin'; Runs fine with Microsoft SQL Server Management Studio Throws error 137 when run against same DB in HeidiSQL

EDIT After browsing around, it seems that there is a workaround to remove the semicolon after the declare.... this makes it run without error, however, it also does not select the expected row, but finds nothing. So, this workaround isn't a workaround at all :)

[KingAlex1985]

Hey steffchep, thanks for your comment. I tested my statement without all semicolons and it worked. It doesent matter if I write the statement only in one row or in 3 rows.

That's a bit strange but it works.

I used this statement for a test on my MS SQL DB: DECLARE @poId BIGINT SET @poId = 27 SELECT * FROM ProductionOrder po WHERE po.productionOrderId = @poId

[steffchep]

Glad it worked for you! As stated, unfortunately in our case it does not... :(

[chrispala]

Hi @ansgarbecker, just confirming I have this same issue. Any query where I declare a variable fails to execute with Error 137. The queries all seem to work fine in other clients (tested with DBeaver, Datagrip, SSMS, SSRS Report Builder and PowerQuery) so I believe this is an issue confined to HeidiSQL.

Thankyou for providing such a great tool - this is literally the only problem I have found in general use and it remains my preferred environment for SQL queries.

[hannoL]

Hi @ansgarbecker , we also had the same problem today.

So the following SQL through an Error:

DECLARE @MyVariable int;
SET @MyVariable = 1;
/* SQL Fehler (137): Must declare the scalar variable "@MyVariable". */
/* Betroffenen Zeilen: 0  Gefundene Zeilen: 0  Warnungen: 0  Dauer von 1 of 3 Abfragen: 0,000 sec. */

Following Code works in heidiSQL correctly and return a 1:

DECLARE @MyVariable int
SET @MyVariable = 1
SELECT @MyVariable;
/* Betroffenen Zeilen: 0  Gefundene Zeilen: 1  Warnungen: 0  Dauer von 1 Abfrage: 0,000 sec. */

But it directly breaks, if you request the Variable two times:

DECLARE @MyVariable int
SET @MyVariable = 1
SELECT @MyVariable;
SELECT @MyVariable;
/* SQL Fehler (137): Must declare the scalar variable "@MyVariable". */
/* Betroffenen Zeilen: 0  Gefundene Zeilen: 1  Warnungen: 0  Dauer von 1 of 2 Abfragen: 0,000 sec. */

Without semicolon also this two statements works fine. To End lines with Semicolons was introduced into Microsoft SQL Server around 2008. In 2016 this was marked as deprecated:

Category	Deprecated feature	Replacement	Feature name
Transact-SQL	Not ending Transact-SQL statements with a semicolon.	End Transact-SQL statements with a semicolon ( ; ).	None

Perhaps you can use Docker or https://www.testcontainers.org/ to reproduce the issue.

[jrkd]

I can confirm this is still a problem.

A lot of MS SQL is written with the semi-colons, and every other SQL viewer is able to handle them so I think its fair to say this is a problem with HediSQL.

[ansgarbecker]

Where the heck does MS SQL store its variables and their declaration, if not in the current session? I see the error on my local SQL Server 2017, but I don't get why it complains the variable hasn't been declared:

DECLARE @a INT;
SET @a = 1; -- SQL Error (137): Die @a-Skalarvariable muss deklariert werden.

In MySQL/MariaDB I can access variables throughout the whole session, once they are assigned:

SET @a = 1;
SELECT @a;

Can somebody shed some light on the difference in SQL Server?

[gsubiran]

Same problem here version 11.2.0.6213. Can't execute query against MSSQL server having a table variable declaration eg: DECLARE @@tableVar table ( ID INT ) Also tested the query on other clients like SQL Server Management Studio and works perfect.

As workaround I created a stored procedure with the query I want to run. After execution ok just deleted SP and that's all

[ansgarbecker]

Note that using the "Send batch in one go" mode on MS SQL fixes that: