DBeaver does not recognize declared variable when executing SQL script on SQL Server

Question

We are using DBeaver Community Edition (v23.2.0.202309041200) as a client connecting to Microsoft SQL Server 2016 (v13.0.5101.9).

The issue happens when executing the below SQL statements in DBeaver as a script.

declare @option as int = 2;

select @option;

The action "Execute SQL script", or by DBeaver hot key Alt+X, always fails on the selecting statement, with error message saying SQL Error [137] [S0002]: Must declare the scalar variable "@option"..

The error does not happen if we highlight the two SQL statements and go with action "Execute SQL query", or by DBeaver hot key Ctrl+Enter.

The error does not happen either if we use Microsoft SQL Server Management Studio, so we do not believe the issue is related to SQL Server.

Our Question:

Is there any way we can use declared variables in DBeaver when executing a SQL script on MSSQL (Microsoft SQL Server)?

We highly appreciate any hints and suggestions.

Answer 1

This is DBeaver's issue as it has its own way of processing sql scripts compared to SSMS or Azure data studio.

In DBeaver, each SQL statement in a script is executed separately and therefore, any variable declared in one statement is not available in following statements.

Workaround : use @set which is a DBeaver command.

@set option = 2
select :option

Note: Please note that this approach may not work if you intend to run your scripts outside of DBeaver, because @set and :variable_name are specific to DBeaver and may not be recognized by other SQL clients or SQL Server itself.

ref: https://github.com/dbeaver/dbeaver/issues/2244#issuecomment-333441954 and https://stackoverflow.com/a/67193585/1387418

Answer 2

Use BEGIN and END. It work for me. Example:

BEGIN
  DECLARE @name VARCHAR(10);
  SET @name = 'My Name';
  SELECT @name AS NAME;
END;

For your case:

BEGIN
  declare @option as int = 2;
  select @option;
END;