MysqlHow-ToBeginner · 4 min read

How to Use DECLARE in MySQL: Syntax and Examples

In MySQL, DECLARE is used inside stored programs like procedures or functions to define local variables, cursors, or condition handlers. You must place DECLARE statements at the start of a block before any other statements. It helps manage data and control flow within stored routines.

📐

Syntax

The DECLARE statement is used inside stored programs to declare variables, cursors, or handlers. It must appear at the beginning of a block before other statements.

Declare a variable: DECLARE var_name datatype [DEFAULT value];
Declare a cursor: DECLARE cursor_name CURSOR FOR select_statement;
Declare a handler: DECLARE handler_type HANDLER FOR condition_statement statement;

sql

DECLARE var_name datatype [DEFAULT value];
DECLARE cursor_name CURSOR FOR select_statement;
DECLARE handler_type HANDLER FOR condition_statement statement;

💻

Example

This example shows how to declare a variable, assign a value, and use it inside a stored procedure.

sql

DELIMITER $$
CREATE PROCEDURE example_declare()
BEGIN
  DECLARE total INT DEFAULT 0;
  DECLARE done INT DEFAULT FALSE;
  DECLARE cur CURSOR FOR SELECT quantity FROM orders;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  read_loop: LOOP
    DECLARE qty INT;
    FETCH cur INTO qty;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SET total = total + qty;
  END LOOP;

  CLOSE cur;
  SELECT total AS total_quantity;
END$$
DELIMITER ;

CALL example_declare();

Output

+----------------+ | total_quantity | +----------------+ | 123 | +----------------+

⚠️

Common Pitfalls

1. Declaring DECLARE outside stored programs: DECLARE only works inside stored procedures, functions, or triggers, not in regular SQL queries.

2. Placing DECLARE after other statements: All DECLARE statements must be at the start of the block before any other code.

3. Forgetting to set handlers for cursor end: Without a handler for NOT FOUND, cursor loops can cause errors.

sql

/* Wrong: DECLARE after statements */
DELIMITER $$
CREATE PROCEDURE wrong_declare()
BEGIN
  SELECT 1;
  DECLARE x INT DEFAULT 0; -- This causes an error
END$$
DELIMITER ;

/* Correct: DECLARE before statements */
DELIMITER $$
CREATE PROCEDURE correct_declare()
BEGIN
  DECLARE x INT DEFAULT 0;
  SELECT 1;
END$$
DELIMITER ;

📊

Quick Reference

Usage	Syntax	Notes
Declare variable	DECLARE var_name datatype [DEFAULT value];	Must be at block start
Declare cursor	DECLARE cursor_name CURSOR FOR select_statement;	Used to iterate query results
Declare handler	DECLARE handler_type HANDLER FOR condition_statement statement;	Handles errors or conditions

✅

Key Takeaways

Use DECLARE only inside stored procedures, functions, or triggers.

Place all DECLARE statements at the start of the block before other code.

Declare variables, cursors, and handlers with DECLARE to manage data and flow.

Always set a handler for cursor NOT FOUND to avoid infinite loops or errors.

DECLARE helps organize and control logic inside MySQL stored programs.