SQL DECLARE

What is SQL DECLARE?

SQL DECLARE introduces a new local variable, cursor, or temporary table within a procedural batch, function, or stored procedure.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL DECLARE: SQL Server (T-SQL), MySQL (inside procedures, triggers, functions), PostgreSQL (PL/pgSQL blocks), Oracle (PL/SQL), MariaDB (procedures/functions). Not supported in plain SQLite.

SQL DECLARE Full Explanation

SQL DECLARE is a procedural statement, not part of core ANSI SQL, that reserves memory for a variable, cursor, or table variable in the current session or execution block. Once declared, the identifier exists only within the scope (batch, function, stored procedure, trigger) where it was created. DECLARE is immediately followed by one or more identifiers, each paired with a data type and an optional default value or query definition.Key behaviors:- Variables hold scalar values and can be reassigned with SET or SELECT.- Cursors declared with DECLARE name CURSOR FOR query create a pointer that can later be OPENed, FETCHed, and CLOSEd.- Table variables (SQL Server only) allocate an in-memory table that behaves like a temporary table but follows variable scope and lifetime.Caveats:- DECLARE is interpreted at parse time, so variable names must be unique within the scope.- Memory use is limited by the database engine’s session limits; excessive table variables can spill to tempdb (SQL Server) or trigger out-of-memory errors.- SQLite lacks native DECLARE; PostgreSQL requires it inside a DECLARE block of PL/pgSQL functions or DO statements, not in plain SQL.

SQL DECLARE Syntax

-- SQL Server variable
declare @variable datatype [ = initial_value ];

-- MySQL stored procedure variable
declare variable_name datatype [ default initial_value ];

-- PostgreSQL PL/pgSQL block
declare
    variable_name datatype [ := initial_value ];
    cursor_name cursor for select_statement;

-- SQL Server table variable
declare @table_variable table (
    column_name datatype [ constraints ]
);

-- Cursor (generic)
declare cursor_name cursor for
select_statement;

SQL DECLARE Parameters

variable_name (identifier) - Name of the variable or table variable
cursor_name (identifier) - Name of the cursor being declared
datatype (data type) - Any valid data type supported by the dialect
initial_value / default (expression) - Optional expression assigned on declaration
select_statement (query) - The query a cursor will iterate over
column_list (column definitions) - Columns for a table variable

Example Queries Using SQL DECLARE

-- 1. Declare and use a scalar variable (SQL Server)
DECLARE @total INT = 0;
SELECT @total = COUNT(*) FROM sales;
PRINT @total;

-- 2. Declare variables and cursor in PostgreSQL function
DO $$
DECLARE
    v_total INTEGER := 0;
    c_sales CURSOR FOR SELECT id, amount FROM sales;
BEGIN
    OPEN c_sales;
    FETCH c_sales INTO v_total, _;
    RAISE NOTICE 'First id is %', v_total;
    CLOSE c_sales;
END $$;

-- 3. Table variable (SQL Server)
DECLARE @temp TABLE(id INT PRIMARY KEY, name NVARCHAR(100));
INSERT INTO @temp VALUES (1,'Alice');
SELECT * FROM @temp;

Expected Output Using SQL DECLARE

@total holds the row count from sales and prints to the console.
Function outputs NOTICE with the first id from the cursor.
Query returns the single row inserted into @temp.

Use Cases with SQL DECLARE

Store intermediate counts or flags while composing complex stored procedures.
Iterate through result sets with a cursor when set-based operations are impractical.
Build small, scoped temp tables that disappear automatically at batch end (SQL Server).
Declare multiple variables for input validation and branching logic in procedural code.

Common Mistakes with SQL DECLARE

Forgetting SET or SELECT to assign a value after declaring a variable.
Using DECLARE outside a procedural context in databases that do not allow it (e.g., plain PostgreSQL SQL client).
Re-declaring the same variable name within the same scope, causing a parse error.
Assuming a table variable has statistics like a normal table, which can lead to poor execution plans (SQL Server).

First Introduced In

Microsoft SQL Server 6.0 (1995)

Frequently Asked Questions

What is the scope of a variable declared with DECLARE?

A variable is visible only inside the batch, stored procedure, function, or trigger where it was declared. It vanishes automatically when execution ends.

Can I declare multiple variables in one DECLARE statement?

Yes. In SQL Server you can separate them with commas:DECLARE @a INT = 1, @b NVARCHAR(50) = 'text';

Does PostgreSQL support DECLARE in plain SQL?

Not directly. DECLARE must appear inside a PL/pgSQL block such as DO $$ ... $$ or within a stored function.

Why is my table variable slowing queries?

Table variables lack full optimizer statistics. For large row counts, SQL Server may choose inefficient plans. Switch to a temporary table with CREATE TABLE #temp for better performance.