2

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.

1
  • 1
    I find DBeaver's docs slightly ambiguous on this. If you remove the semicolon ; from the first line, such that it's written as declare @option as int = 2, does the script work with ALT + X?
    – J.D.
    Oct 4, 2023 at 0:12

2 Answers 2

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

1
  • "In DBeaver, each SQL statement in a script is executed separately" - I don't believe this to be true, based on their (somewhat ambiguous) documentation on the shortcuts which has a separate shortcut for "Execute SQL script" which is ALT+X from the shortcut for "Execute SQL statement" which is CTRL+ENTER. The docs sound like what OP wants to accomplish should work in the manner that they're trying.
    – J.D.
    Oct 4, 2023 at 1:40
0

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;
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.