Dbeaver, How to declare variables & use them?

sql

prompt-engineering

best-practices

sql-queries

byAnton Shumikhin·Jan 16, 2025

In DBeaver, use SET to assign values to variables instantly for MySQL:

SET @var := 'value'; SELECT @var; -- Disappointingly, "value" is not a Harry Potter spell.

For executing blocks with variables in PostgreSQL, use DO with RAISE NOTICE for output:

DO $$ DECLARE my_var text := 'value'; BEGIN RAISE NOTICE '%', my_var; END $$; -- Sending "value" to PostgreSQL's Upside-Down.

In SQL Server, DECLARE and SELECT settings handle variable declaration:

DECLARE @var VARCHAR(100) = 'value'; SELECT @var; -- SQL Server:

The syntax adapts based on the SQL database.

Steps to variable processing

To use variables in DBeaver, first turn on variable processing. Walk through the steps:

Open DBeaver.
Navigate to Window > Preferences.
Find SQL Processing (under SQL Editor or Database).
Look for Variables and make sure the Enable Variables or comparative choice is checked.

It's the secret handshake for DBeaver to recognise placeholders.

Variable play: DECLARE and SET

Setting the stage right, proceed to variable declaration:

Declare variables using @set:

@set variable_name = 'value'; -- @set: The Dumbledore of declaring variables.

To use these variables in your queries, reference them ${variable_name}:

SELECT * FROM users WHERE username = ${variable_name}; -- What's in a ${variable_name}? Would a rose smell as sweet?

A few pro moves in variable declaration

Use casting:

@set int_variable = CAST(1234 AS INTEGER); -- Cast the data, like Harry Potter casts "Wingardium Leviosa".

For a BEGIN...END block within SQL Server, use DECLARE and SET:

BEGIN
    DECLARE @local_var INT;
    SET @local_var = 1234;
    -- Query using @local_var beyond this.
END

: prefix variables denote named parameters:

EXECUTE my_procedure(:param1, :param2); -- Prepare a magical potion using :ingredients.

Survival guide: Common errors and fixes

Using variables can sometimes trip you up:

Scope: Variables declared within a block are not accessible outside. Go global.
Uninitialised variables mean chaos. Boot them with an initial value.
Data types: Ensure you don't mismatch or use incorrect data types.

Facing an issue? Use the DBeaver documentation, community forum, or GitHub issues page to help resolve it.

Using the @set command efficiently

Use @set to set default fieldnames, saving time:

-- Assuming `name` field is often queried with 'John Doe'
@set default_name = 'John Doe';
SELECT * FROM customers WHERE name = ${default_name}; -- Here comes John Doe!