SQL DECLARE Variable: Complete Guide | Examples in All 5 Databases

Variables are the heart of SQL programming and the most powerful tools inside SQL programming. SQL DECLARE Variable allows you to store data temporarily, reuse values in multiple places, calculate totals, track counters, and build dynamic logic in complex scripts.

But this concept is not complex; the syntax for SQL DECLARE Variable differs dramatically across SQL databases, which mostly leads to confusion and unexpected errors.

If you’ve tried SQL Server syntax on MySQL or PL/SQL syntax on PostgreSQL, you can realize that it is a struggle. Therefore, this guide will explain everything you need to know about SQL DECLARE Variable across major databases in SQL Server, MySQL, PostgreSQL, and Oracle.

This article includes a completed, practical tutorial with examples, best practices, and common pitfalls — also a guide written in a blend of professional accuracy with friendly, simple explanation.

Let’s get started.

🔍 What Does DECLARE Do in SQL? ( Snippet)

The SQL DECLARE Variable creates a variable in memory for below cases. So you can store a temporary value and later use it in your SQL code like a script, block, function, or procedure.

Variables used in:

• Store values like numbers, text, or dates
• Save the results or output of queries
• Perform calculations on a script, block, function, or procedure.
• Control flow with loops
• Build static or dynamic flows
• Avoid repeating expressions and helping to help improve code readability

All SQL engines use and work with variables to different extend — but each database has different syntax, rules, and scopes. Knowing those differences is fundamental for writing good and bug-free SQL code.

📊 SQL Variable Syntax Comparison (Review)

This table helps to see the comparison in a single picture. This is the basic compare for INT type SQL DECLARE Variable

🟦 1. Declaring Variables in SQL Server (T-SQL)

SQL Server databases define variables like below using the @ sign. Inline Initialization improves readability and reduces errors.

🔹 Basic Syntaxes

--
🔹 Basic Syntax
DECLARE @variable_name datatype;
Example:
DECLARE @age INT;
DECLARE @name VARCHAR(100);
________________________________________
🔹 Inline Initialization (default value initialization)
DECLARE @count INT = 10;
DECLARE @title VARCHAR(50) = 'Manager';
________________________________________
🔹 Multiple Variables in same Block
DECLARE 
    @empId INT = 101,
    @salary DECIMAL(10,2) = 5500.00,
    @active BIT = 1;
________________________________________
--

🔹 Assigning Values: SET vs SELECT

SQL Server supports two assignment methods. When to use is SET for clarity (one variable at a time). SELECT for performance and multi-variable assignment

--
🔹 Using SET (ANSI standard):
SET @tot = 100;
🔹 Using SELECT (SQL Server specific):
SELECT @tot = SUM(amount) FROM sales;
🔹 Assign multiple variables at once
SELECT 
    @id = id,
    @name = name
FROM employees
WHERE id = 1;
________________________________________
🔹 SQL Server Example: Parameter Calculation
DECLARE @rate DECIMAL(5,2) = 0.10;
DECLARE @price DECIMAL(10,2) = 200;
DECLARE @amount DECIMAL(10,2);

SET @amount = @price * @rate;

SELECT @amount AS Discount;
________________________________________
--

🟩 2. Declaring Variables in MySQL

MySQL handles variables another way, and compared to SQL Server, this can confuse developers.

🔹 DECLARE inside stored programs

You cannot run this code outside the function; this can fail with “DECLARE is not allowed outside stored functions or triggers.”

--
DECLARE x INT;
--

🔹 MySQL DECLARE Syntax

--
DECLARE variable_name datatype DEFAULT value;
Example:
DECLARE total INT DEFAULT 0;
DECLARE userName VARCHAR(50) DEFAULT 'Guest';
________________________________________

🔹 Using SELECT INTO

This is the most common variable assignment in MySQL. This will assign values directly to the variable when executing the query.

--
SELECT COUNT(*) INTO totalUsers FROM users;
--

🔹 Example: MySQL Stored Procedure with Variables

--
DELIMITER //
CREATE PROCEDURE GetProductCount()
BEGIN
  DECLARE product_count INT DEFAULT 0;

  SELECT COUNT(*) INTO product_count FROM products;

  SELECT product_count AS TotalProducts;
END //
DELIMITER ;
________________________________________
--

🔹 DECLARE Order Matters

MySQL requires: The Order of the syntax is critical for most databases. Here also need to SQL DECLARE Variable first.

1. DECLARE statements first
2. Followed by other logic

--
--This will fail:
SET x = 10;
DECLARE x INT;
--

🟧 3. Declaring Variables in PostgreSQL (PL/pgSQL)

PostgreSQL also supports variables, but does not support DECLARE in plain SQL queries.
Variables work only in:

• DO blocks
• Functions
• Procedures

PostgreSQL supports the following assignments:

• :=   — common
• =    — allowed
• DEFAULT value

🔹 Basic Syntaxes

--
🔹 Basic Syntax
DO $$
DECLARE
  counter INT := 0;
BEGIN
  counter := counter + 5;
  RAISE NOTICE 'Counter value: %', counter;
END$$;
________________________________________
🔹 Using SELECT INTO
SELECT COUNT(*) INTO totalUsers FROM users;
This bit changed compared to SQL Server. PostgreSQL uses INTO keyword after the SELECT list.
________________________________________
🔹 PostgreSQL: Login Attempts
DO $$
DECLARE
  attempts INT := 0;
BEGIN
  SELECT login_count INTO attempts
  FROM audit
  WHERE user_id = 1;

  RAISE NOTICE 'Login attempts: %', attempts;
END$$;
________________________________________
--

🟥 4. Declaring Variables in Oracle (PL/SQL)

This is my favorite. Oracle PL/SQL is block-structured, and SQL variables are defined at the top of the block. Note: Check SQL Version before using some variable type may not support older versions.

Tip: Oracle variable type takes memory space. Therefore, we use a better length of variable type to improve the performance of queries.

--
🔹 Basic Syntax
DECLARE
  var_name datatype := value;
BEGIN
  -- execution block
END;
________________________________________
🔹 Example: Basic Variables
DECLARE
  empName VARCHAR2(50) := 'John';
  salary NUMBER := 5000;
BEGIN
  DBMS_OUTPUT.PUT_LINE(empName || ' earns ' || salary);
END;
________________________________________
🔹 Oracle SELECT INTO Example
DECLARE
  total_emps NUMBER;
BEGIN
  SELECT COUNT(*) INTO total_emps FROM employees;
  DBMS_OUTPUT.PUT_LINE('Total: ' || totalEmployees);
END;
--

🔹 Oracle Variable Types

• NUMBER
• VARCHAR2
• BOOLEAN
• DATE
• TIMESTAMP

This variety makes PL/SQL very flexible. There are a lot more types. You can see all those Oracle data types here.

5. DECLARE vs SET vs SELECT All Databases

Key takeaway:
DECLARE is used to create variables in a block.
SET assigns direct values to variables.
SELECT assigns values from queries into variables.

⚠ 6. SQL  Declare Variable | Errors and Fixes

MySQL: DECLARE not allowed outside the begin and end

Cause: DECLARE used outside a function/procedure
Fix: Wrap inside:

--
BEGIN
  DECLARE x INT;
END;
--

PostgreSQL INTO clause required: once assigned, FROM select INTO keyword is required

Fix: Always use SELECT ... INTO variable

🧠 7. Best Practices for Declaring SQL Variables

✔ Meaningful variable names: This is used to write more readable code for other developers.
✔ Initialize variables to avoid NULL: null values can affect with code block. Make sure to initialize if default values are available.
✔ Keep scope as small as possible: keep the block or function small. It may be more readable
✔ Avoid declaring unused variables: this causes more headaches in big logics
✔ Follow naming style (camelCase, snake_case, etc.)
✔ Don’t overload variables with multiple meanings: the solution is a simple block or unit of code
✔ Prefer strong data types (not all VARCHAR2): as I said earlier, there are a lot of data types that take a smaller memory size, and performance is good on the engine.

These habits improve maintainability and reduce bugs. Also, those are simple tips, not heavy processors. But those are making the code more effective in the final

🏁 Conclusion

Understanding how to SQL declare variables is a must. Because database issues are raised might be because of wrong syntax. Although the concept is universal, each database engine has its own syntax and processors. Therefore, knowing each syntax may help with coding.

By learning SQL Server’s @ syntax, MySQL’s stored-program restrictions, PostgreSQL’s PL/pgSQL blocks, and Oracle’s PL/SQL style, you can write cleaner, more powerful, and more portable SQL code.

Frequently Asked Questions about SQL DECLARE Variables

1. What happens when using DECLARE in SQL?

It defines a new variable in SQL — use a variable to store a value temporarily during script or procedure execution.

2. Can I use DECLARE in a plain SQL query (outside the procedure)?

It depends on the database you’re using— some allow it (e.g., SQL Server), others need to declare those inside the block, like functions and procedures.

3. What is the difference between using SET and SELECT to assign a variable?

SET assigns a value explicitly; SELECT @var = … assigns a value based on a query’s result or the output of the query

4. Can variables store results from a query?

Yes possible. For example:

--
DECLARE @cnt INT;
SELECT @cnt = COUNT(*) FROM users;
--

5. Do SQL variables exist after the script finishes execution?

No, it’s temporarily created in memory until the block completes — variables are temporary and valid only during the session, script, or procedure execution; they do not persist beyond.

6. Can I declare multiple variables in a single statement?

Yes, many SQL support multiple declarations in one line or block. Example:

DECLARE @id INT, @name VARCHAR(50), @age INT;

7. DECLARE outside a procedure or block in some databases (e.g., MySQL)?

This is a rule of the database. Because some SQL engines restrict the use of DECLARE to stored procedures/functions or blocks

8. What data types can a SQL variable hold?

Typical types: integers, decimals, strings (text), dates/times — according to database support. Please look Oracle type in above

9. Are variable names case‑sensitive?

Usually not — SQL variable names are generally case-insensitive, but names are crucial when writing good code. Once you have a bug, readability is more important than writing big logic in a single block.

10. Can using variables improve code readability compared to hard‑coded literals?

Yes — variables make code more maintainable and easier to update, variables contain different dynamic values at execution times. Therefore important to use meaningful naming and standards for future use.  

Ref https://www.sqlshack.com/sql-variables-basics-and-usage/