Search code examples
sqlsql-serversql-server-2019heidisql

SQL Error (137): Must declare the scalar variable "@sql"


I was unable to run the following SQL commands. First I thought that it might be because "max" is not defined but on the internet there SQL scripts with "max". I cannot understand what is wrong with the script. May be version of the SQL Server is wrong. I get an error:

SQL Error (137): Must declare the scalar variable "@sql"

Code:

DECLARE @sql nvarchar(MAX) = ''

SELECT @sql += 'ALTER TABLE ' + Quotename(tbl.name) + '  DROP CONSTRAINT ' + Quotename(cons.name) + ';'
 FROM SYS.DEFAULT_CONSTRAINTS cons
 JOIN SYS.COLUMNS col ON col.default_object_id = cons.object_id
 JOIN SYS.TABLES tbl ON tbl.object_id = col.object_id
 WHERE col.[name] IN ('id_card_number');

--PRINT @sql
EXEC Sp_executesql @sql

MSSQL query

HeidiSQL


Solution

  • You are using HeidiSQL, which has a choice between Send queries one by one or Send batch in one go.

    The latter must be used to achieve compatibility with what is considered normal SSMS / SQL Server behaviour.

    See answer in https://www.heidisql.com/forum.php?t=38756#p38764

    Screenshot:
    enter image description here