SQL Variables, Control Flow & Error Handling

Learn how to use SQL variables, conditional logic with IF/ELSE, WHILE loops, and error handling across PostgreSQL, MySQL, and SQL Server for procedural database programming.

Standard SQL is a declarative language: you describe what data you want, and the database figures out how to get it. But when you write stored procedures, functions, triggers, or ad-hoc scripts that need conditional logic, loops, and error recovery, you step into the world of procedural SQL. This is where SQL variables control flow and error handling become essential.

Each major database has its own procedural extension: PostgreSQL has PL/pgSQL, MySQL has its stored program language, and SQL Server has T-SQL. The concepts are the same across all three, but the syntax differs. This guide teaches you the core building blocks: declaring and using variables, branching with IF/ELSE, looping with WHILE, and handling errors gracefully so your code never leaves the database in a broken state.

Every concept is demonstrated side by side across PostgreSQL, MySQL, and SQL Server so you can apply it immediately regardless of your database.

The Sample Data

We will use these tables throughout the guide:

employees table:

id	name	department	salary	hire_date	is_active
1	Alice	Engineering	95000.00	2019-03-15	true
2	Bob	Engineering	88000.00	2021-06-01	true
3	Charlie	Sales	72000.00	2018-11-20	true
4	Diana	Sales	68000.00	2022-01-10	false
5	Eve	Marketing	74000.00	2020-08-05	true
6	Frank	Marketing	70000.00	2023-02-14	true

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,             -- SERIAL = auto-increment
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    hire_date DATE NOT NULL,
    is_active BOOLEAN NOT NULL
);

INSERT INTO employees (name, department, salary, hire_date, is_active) VALUES
('Alice',   'Engineering', 95000.00, '2019-03-15', TRUE),
('Bob',     'Engineering', 88000.00, '2021-06-01', TRUE),
('Charlie', 'Sales',       72000.00, '2018-11-20', TRUE),
('Diana',   'Sales',       68000.00, '2022-01-10', FALSE),
('Eve',     'Marketing',   74000.00, '2020-08-05', TRUE),
('Frank',   'Marketing',   70000.00, '2023-02-14', TRUE);

audit_log table:

id	action	details	created_at

CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,               -- SERIAL for auto-increment
    action VARCHAR(50) NOT NULL,
    details VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP  -- PostgreSQL timestamp
);

salary_adjustments table:

id	employee_id	old_salary	new_salary	reason	adjusted_at

CREATE TABLE salary_adjustments (
    id SERIAL PRIMARY KEY,              -- SERIAL = auto-increment
    employee_id INT NOT NULL,
    old_salary DECIMAL(10,2) NOT NULL,
    new_salary DECIMAL(10,2) NOT NULL,
    reason VARCHAR(100) NOT NULL,
    adjusted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_salary_employee FOREIGN KEY (employee_id)
        REFERENCES employees(id)
);

Declaring Variables

Variables store intermediate values during procedural execution. They are the foundation for everything else: branching decisions, loop counters, query results, and error messages.

PostgreSQL (PL/pgSQL)

Variables are declared in a DECLARE block before BEGIN:

DO $$
DECLARE
    v_name       VARCHAR(100);
    v_salary     DECIMAL(10,2);
    v_count      INT := 0;           -- Initialize with a value
    v_is_active  BOOLEAN := TRUE;
    v_today      DATE := CURRENT_DATE;
BEGIN
    v_name := 'Alice';
    v_salary := 95000.00;
    v_count := v_count + 1;

    RAISE NOTICE 'Name: %, Salary: %, Count: %', v_name, v_salary, v_count;
END;
$$;

Output:

NOTICE:  Name: Alice, Salary: 95000.00, Count: 1

Key points:

Variables live in the DECLARE section
Assignment uses :=
RAISE NOTICE prints debug output
DO $$ ... $$; runs an anonymous block without creating a stored function

MySQL

Variables are declared with DECLARE inside a BEGIN...END block (only within stored programs):

DELIMITER //

CREATE PROCEDURE demo_variables()
BEGIN
    DECLARE v_name      VARCHAR(100);
    DECLARE v_salary    DECIMAL(10,2);
    DECLARE v_count     INT DEFAULT 0;
    DECLARE v_is_active BOOLEAN DEFAULT TRUE;
    DECLARE v_today     DATE DEFAULT CURRENT_DATE;

    SET v_name = 'Alice';
    SET v_salary = 95000.00;
    SET v_count = v_count + 1;

    SELECT v_name AS name, v_salary AS salary, v_count AS count;
END //

DELIMITER ;

CALL demo_variables();

Output:

name	salary	count
Alice	95000.00	1

Key points:

DECLARE must appear before any executable statements
Assignment uses SET variable = value
Default values use the DEFAULT keyword

SQL Server (T-SQL)

Variables are declared with DECLARE anywhere in the batch:

DECLARE @v_name      VARCHAR(100);
DECLARE @v_salary    DECIMAL(10,2);
DECLARE @v_count     INT = 0;
DECLARE @v_is_active BIT = 1;
DECLARE @v_today     DATE = GETDATE();

SET @v_name = 'Alice';
SET @v_salary = 95000.00;
SET @v_count = @v_count + 1;

-- Multiple declarations in one statement
DECLARE @a INT = 1, @b INT = 2, @c INT = 3;

SELECT @v_name AS name, @v_salary AS salary, @v_count AS [count];

Key points:

Variables are prefixed with @
DECLARE can appear anywhere, not just at the top
Assignment uses SET @var = value or SELECT @var = value

Syntax Comparison

Feature	PostgreSQL	MySQL	SQL Server
Prefix	None (`v_name`)	None (`v_name`)	`@` (`@v_name`)
Declaration	`DECLARE v TYPE;`	`DECLARE v TYPE;`	`DECLARE @v TYPE;`
Default	`v TYPE := value;`	`DECLARE v TYPE DEFAULT value;`	`DECLARE @v TYPE = value;`
Assignment	`v := value;`	`SET v = value;`	`SET @v = value;`
Placement	`DECLARE` block before `BEGIN`	Start of `BEGIN...END`	Anywhere in batch
Debug output	`RAISE NOTICE`	`SELECT`	`PRINT` or `SELECT`

Assigning Query Results to Variables

Capturing a query result into a variable is one of the most common operations:

PostgreSQL

DO $$
DECLARE
    v_salary    DECIMAL(10,2);
    v_emp_name  VARCHAR(100);
    v_emp_count INT;
BEGIN
    SELECT salary, name INTO v_salary, v_emp_name
    FROM employees WHERE id = 1;

    SELECT COUNT(*) INTO v_emp_count
    FROM employees WHERE is_active = TRUE;

    RAISE NOTICE '% earns %. Active employees: %', v_emp_name, v_salary, v_emp_count;
END;
$$;

MySQL

SELECT salary, name INTO v_salary, v_emp_name
FROM employees WHERE id = 1;

SELECT COUNT(*) INTO v_emp_count
FROM employees WHERE is_active = TRUE;

SQL Server

SELECT @v_salary = salary, @v_emp_name = name
FROM employees WHERE id = 1;

SELECT @v_emp_count = COUNT(*)
FROM employees WHERE is_active = 1;

SELECT INTO with Multiple Rows

If the SELECT returns more than one row:

PostgreSQL: Error: query returned more than one row
MySQL: Error: Result consisted of more than one row
SQL Server: Silently assigns the last row's values (no error!)

-- SQL Server: Dangerous! Silently takes the last row
DECLARE @name VARCHAR(100);
SELECT @name = name FROM employees;  -- No WHERE clause!
-- @name = 'Frank' (last row), no error raised

-- Always use WHERE or TOP to ensure a single row
SELECT @name = name FROM employees WHERE id = 1;

IF / ELSE

Conditional branching lets your procedural code make decisions based on variable values or query results.

PostgreSQL

DO $$
DECLARE
    v_salary DECIMAL(10,2);
    v_band   VARCHAR(20);
BEGIN
    SELECT salary INTO v_salary FROM employees WHERE id = 1;

    IF v_salary >= 90000 THEN
        v_band := 'Senior';
    ELSIF v_salary >= 70000 THEN
        v_band := 'Mid-Level';
    ELSIF v_salary >= 50000 THEN
        v_band := 'Junior';
    ELSE
        v_band := 'Entry';
    END IF;

    RAISE NOTICE 'Salary: %, Band: %', v_salary, v_band;
END;
$$;

NOTICE:  Salary: 95000.00, Band: Senior

Note: PostgreSQL uses ELSIF (not ELSEIF).

MySQL

DELIMITER //

CREATE PROCEDURE classify_employee(IN p_employee_id INT)
BEGIN
    DECLARE v_salary DECIMAL(10,2);
    DECLARE v_band   VARCHAR(20);

    SELECT salary INTO v_salary FROM employees WHERE id = p_employee_id;

    IF v_salary >= 90000 THEN
        SET v_band = 'Senior';
    ELSEIF v_salary >= 70000 THEN
        SET v_band = 'Mid-Level';
    ELSEIF v_salary >= 50000 THEN
        SET v_band = 'Junior';
    ELSE
        SET v_band = 'Entry';
    END IF;

    SELECT v_salary AS salary, v_band AS band;
END //

DELIMITER ;

SQL Server

DECLARE @v_salary DECIMAL(10,2);
DECLARE @v_band   VARCHAR(20);

SELECT @v_salary = salary FROM employees WHERE id = 1;

IF @v_salary >= 90000
    SET @v_band = 'Senior';
ELSE IF @v_salary >= 70000
    SET @v_band = 'Mid-Level';
ELSE IF @v_salary >= 50000
    SET @v_band = 'Junior';
ELSE
    SET @v_band = 'Entry';

SELECT @v_salary AS salary, @v_band AS band;

SQL Server: BEGIN/END with IF

In SQL Server, IF without BEGIN/END only covers the single next statement. For multiple statements, you must wrap them:

-- WRONG: Only the SET executes conditionally; the INSERT always runs
IF @v_salary > 90000
    SET @v_band = 'Senior';
    INSERT INTO audit_log (action) VALUES ('Senior detected');  -- Always runs!

-- CORRECT: Both statements are conditional
IF @v_salary > 90000
BEGIN
    SET @v_band = 'Senior';
    INSERT INTO audit_log (action) VALUES ('Senior detected');
END

PostgreSQL and MySQL do not have this problem because their IF blocks end explicitly with END IF.

Practical Example: Conditional Salary Adjustment

-- PostgreSQL
CREATE OR REPLACE PROCEDURE adjust_salary(
    IN p_employee_id INT,
    IN p_adjustment_type VARCHAR(20),
    INOUT p_result VARCHAR(200) DEFAULT ''
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_current_salary DECIMAL(10,2);
    v_new_salary     DECIMAL(10,2);
    v_pct            DECIMAL(5,2);
BEGIN
    SELECT salary INTO v_current_salary
    FROM employees WHERE id = p_employee_id;

    IF v_current_salary IS NULL THEN
        p_result := 'ERROR: Employee not found';
        RETURN;
    END IF;

    IF p_adjustment_type = 'promotion' THEN
        v_pct := 15.0;
    ELSIF p_adjustment_type = 'annual' THEN
        v_pct := 5.0;
    ELSIF p_adjustment_type = 'cost_of_living' THEN
        v_pct := 3.0;
    ELSE
        p_result := 'ERROR: Unknown adjustment type: ' || p_adjustment_type;
        RETURN;
    END IF;

    v_new_salary := ROUND(v_current_salary * (1 + v_pct / 100), 2);

    UPDATE employees SET salary = v_new_salary WHERE id = p_employee_id;

    INSERT INTO salary_adjustments (employee_id, old_salary, new_salary, reason, adjusted_at)
    VALUES (p_employee_id, v_current_salary, v_new_salary, p_adjustment_type, NOW());

    p_result := FORMAT('SUCCESS: %s → %s (%s%%)', v_current_salary, v_new_salary, v_pct);
END;
$$;

And call:

CALL adjust_salary(3, 'promotion', NULL);

p_result
SUCCESS: 72000.00 → 82800.00 (15.00%)

WHILE Loops

WHILE loops repeat a block of code as long as a condition remains true. This is the most universal loop construct, supported by all three databases.

PostgreSQL

DO $$
DECLARE
    v_counter INT := 1;
    v_total   DECIMAL(10,2) := 0;
BEGIN
    WHILE v_counter <= 5 LOOP
        v_total := v_total + v_counter * 100;
        RAISE NOTICE 'Iteration %: total = %', v_counter, v_total;
        v_counter := v_counter + 1;
    END LOOP;

    RAISE NOTICE 'Final total: %', v_total;
END;
$$;

NOTICE:  Iteration 1: total = 100
NOTICE:  Iteration 2: total = 300
NOTICE:  Iteration 3: total = 600
NOTICE:  Iteration 4: total = 1000
NOTICE:  Iteration 5: total = 1500
NOTICE:  Final total: 1500

MySQL

DELIMITER //

CREATE PROCEDURE count_loop()
BEGIN
    DECLARE v_counter INT DEFAULT 1;
    DECLARE v_total   DECIMAL(10,2) DEFAULT 0;

    WHILE v_counter <= 5 DO
        SET v_total = v_total + v_counter * 100;
        SET v_counter = v_counter + 1;
    END WHILE;

    SELECT v_total AS final_total;
END //

DELIMITER ;

SQL Server

DECLARE @counter INT = 1;
DECLARE @total   DECIMAL(10,2) = 0;

WHILE @counter <= 5
BEGIN
    SET @total = @total + @counter * 100;
    PRINT 'Iteration ' + CAST(@counter AS VARCHAR) + ': total = ' + CAST(@total AS VARCHAR);
    SET @counter = @counter + 1;
END

SELECT @total AS final_total;

Loop Syntax Comparison

Feature	PostgreSQL	MySQL	SQL Server
Start	`WHILE cond LOOP`	`WHILE cond DO`	`WHILE cond BEGIN`
End	`END LOOP;`	`END WHILE;`	`END`
Break	`EXIT;`	`LEAVE label;`	`BREAK;`
Continue	`CONTINUE;`	`ITERATE label;`	`CONTINUE;`

Breaking and Continuing

PostgreSQL

DO $$
DECLARE
    v_counter INT := 0;
BEGIN
    LOOP
        v_counter := v_counter + 1;

        IF v_counter % 2 = 0 THEN
            CONTINUE;  -- Skip even numbers
        END IF;

        RAISE NOTICE 'Odd: %', v_counter;

        EXIT WHEN v_counter >= 9;  -- Break at 9
    END LOOP;
END;
$$;

NOTICE:  Odd: 1
NOTICE:  Odd: 3
NOTICE:  Odd: 5
NOTICE:  Odd: 7
NOTICE:  Odd:

MySQL

MySQL requires labels for LEAVE (break) and ITERATE (continue):

DELIMITER //
CREATE PROCEDURE loop_demo()
BEGIN
    DECLARE v_counter INT DEFAULT 0;

    my_loop: LOOP
        SET v_counter = v_counter + 1;

        IF v_counter MOD 2 = 0 THEN
            ITERATE my_loop;  -- Skip even numbers
        END IF;

        IF v_counter >= 9 THEN
            LEAVE my_loop;    -- Break at 9
        END IF;

        SELECT v_counter AS odd_number;
    END LOOP my_loop;
END //
DELIMITER ;

SQL Server

DECLARE @counter INT = 0;

WHILE 1 = 1  -- Infinite loop
BEGIN
    SET @counter = @counter + 1;

    IF @counter % 2 = 0
        CONTINUE;

    PRINT 'Odd: ' + CAST(@counter AS VARCHAR);

    IF @counter >= 9
        BREAK;
END

FOR Loops (PostgreSQL Only)

PostgreSQL offers FOR loops not available in MySQL or SQL Server:

DO $$
DECLARE
    v_emp RECORD;
BEGIN
    -- Numeric FOR loop
    FOR i IN 1..5 LOOP
        RAISE NOTICE 'Number: %', i;
    END LOOP;

    -- Query result FOR loop
    FOR v_emp IN
        SELECT name, salary FROM employees WHERE is_active = TRUE ORDER BY salary DESC
    LOOP
        RAISE NOTICE '% earns %', v_emp.name, v_emp.salary;
    END LOOP;
END;
$$;

Alice earns 95000.00
Bob earns 88000.00
Charlie earns 82800.00
Eve earns 74000.00
Frank earns 70000.00

The RECORD type holds a row of any structure. Access columns with dot notation: v_emp.name, v_emp.salary.

Cursors for Row-by-Row Processing (MySQL / SQL Server)

For MySQL and SQL Server (which lack query-based FOR loops), cursors provide equivalent row-by-row iteration:

MySQL

DELIMITER //
CREATE PROCEDURE process_employees()
BEGIN
    DECLARE v_name   VARCHAR(100);
    DECLARE v_salary DECIMAL(10,2);
    DECLARE v_done   INT DEFAULT FALSE;

    DECLARE emp_cursor CURSOR FOR
        SELECT name, salary FROM employees WHERE is_active = TRUE;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

    OPEN emp_cursor;

    read_loop: LOOP
        FETCH emp_cursor INTO v_name, v_salary;
        IF v_done THEN LEAVE read_loop; END IF;
        -- Process each row here
    END LOOP;

    CLOSE emp_cursor;
END //
DELIMITER ;

SQL Server

DECLARE @name VARCHAR(100), @salary DECIMAL(10,2);

DECLARE emp_cursor CURSOR FOR
    SELECT name, salary FROM employees WHERE is_active = 1;

OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @name, @salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @name + ': $' + CAST(@salary AS VARCHAR);
    FETCH NEXT FROM emp_cursor INTO @name, @salary;
END

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

Cursors Are Usually a Last Resort

Cursors process rows one at a time, which is fundamentally at odds with SQL's set-based strengths. A single UPDATE is almost always faster than a cursor loop:

-- SLOW: Cursor updates rows one by one
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE employees SET salary = salary * 1.05 WHERE id = @id;
    FETCH NEXT...
END

-- FAST: Set-based update (all rows at once)
UPDATE employees SET salary = salary * 1.05;

Use cursors only when you need complex per-row conditional logic that truly cannot be expressed in a single SQL statement.

Error Handling

Error handling prevents your procedural code from crashing and leaving the database in an inconsistent state. Each database has its own mechanism, but the goals are identical: catch errors, roll back partial changes, and report what went wrong.

PostgreSQL: EXCEPTION Blocks

PostgreSQL uses EXCEPTION blocks at the end of a BEGIN...END:

DO $$
DECLARE
    v_result DECIMAL(10,2);
BEGIN
    v_result := 100 / 0;  -- Division by zero!
    RAISE NOTICE 'Result: %', v_result;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Error: Cannot divide by zero';
    WHEN OTHERS THEN
        RAISE NOTICE 'Unexpected error: % (%)', SQLERRM, SQLSTATE;
END;
$$;

NOTICE:  Error: Cannot divide by zero

Catching Specific Errors

CREATE OR REPLACE PROCEDURE safe_insert_employee(
    IN p_id INT,
    IN p_name VARCHAR(100),
    IN p_department VARCHAR(50),
    IN p_salary DECIMAL(10,2),
    INOUT p_status VARCHAR(200) DEFAULT ''
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO employees (id, name, department, salary, is_active)
    VALUES (p_id, p_name, p_department, p_salary, TRUE);

    p_status := 'SUCCESS: Employee created';

EXCEPTION
    WHEN unique_violation THEN
        p_status := 'ERROR: Employee ID ' || p_id || ' already exists';
    WHEN not_null_violation THEN
        p_status := 'ERROR: Required field is missing';
    WHEN check_violation THEN
        p_status := 'ERROR: Data violates a check constraint';
    WHEN OTHERS THEN
        p_status := 'ERROR: ' || SQLSTATE || ' - ' || SQLERRM;
END;
$$;

CALL safe_insert_employee(1, 'Duplicate', 'Sales', 60000, NULL);

p_status
ERROR: Employee ID 1 already exists

Important: EXCEPTION Blocks Create Savepoints

In PostgreSQL, each EXCEPTION block implicitly creates a savepoint. If an error occurs, changes inside the block are automatically rolled back before the handler executes:

DO $$
BEGIN
    INSERT INTO audit_log (action, details) VALUES ('test', 'entry 1');
    INSERT INTO audit_log (action, details) VALUES ('test', 'entry 2');
    RAISE EXCEPTION 'Intentional error';
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Both inserts were automatically rolled back';
END;
$$;
-- audit_log has NO new entries

Common PostgreSQL error conditions:

Condition	Fires When
`unique_violation`	UNIQUE constraint violated
`foreign_key_violation`	FOREIGN KEY constraint violated
`not_null_violation`	NOT NULL constraint violated
`check_violation`	CHECK constraint violated
`division_by_zero`	Division by zero
`OTHERS`	Any unhandled error (catch-all)

MySQL: DECLARE HANDLER

MySQL uses handler declarations that must appear at the start of the BEGIN...END block:

DELIMITER //

CREATE PROCEDURE safe_transfer(
    IN p_from_id INT,
    IN p_to_id INT,
    IN p_amount DECIMAL(10,2),
    OUT p_status VARCHAR(200)
)
BEGIN
    DECLARE v_balance DECIMAL(10,2);
    DECLARE v_err_msg VARCHAR(200);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 v_err_msg = MESSAGE_TEXT;
        ROLLBACK;
        SET p_status = CONCAT('ERROR: ', v_err_msg);
    END;

    START TRANSACTION;

    SELECT balance INTO v_balance
    FROM accounts WHERE id = p_from_id FOR UPDATE;

    IF v_balance < p_amount THEN
        ROLLBACK;
        SET p_status = 'ERROR: Insufficient funds';
    ELSE
        UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_id;
        UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_id;
        COMMIT;
        SET p_status = 'SUCCESS';
    END IF;
END //

DELIMITER ;

Handler Types

Type	Behavior After Handling
`EXIT HANDLER`	Exits the current `BEGIN...END` block
`CONTINUE HANDLER`	Continues at the next statement after the error

Conditions You Can Handle

Condition	Meaning
`SQLEXCEPTION`	Any error
`SQLWARNING`	Any warning
`NOT FOUND`	SELECT INTO returned no rows, or cursor exhausted
`SQLSTATE '23000'`	Integrity constraint violation
`1062`	MySQL error number for duplicate key

-- Handle a specific MySQL error number
DECLARE EXIT HANDLER FOR 1062
BEGIN
    SET p_status = 'ERROR: Duplicate entry';
END;

MySQL Does Not Auto-Rollback on Error

Unlike PostgreSQL, MySQL's error handlers do not automatically roll back the transaction. You must explicitly call ROLLBACK:

-- WRONG: Partial changes may persist
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    SET p_status = 'Error occurred';
    -- Transaction still open with partial changes!
END;

-- CORRECT: Always rollback
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    SET p_status = 'Error occurred';
END;

SQL Server: TRY / CATCH

SQL Server uses structured TRY/CATCH blocks, the closest to modern programming languages:

CREATE PROCEDURE safe_transfer
    @p_from_id INT,
    @p_to_id INT,
    @p_amount DECIMAL(10,2),
    @p_status VARCHAR(200) OUTPUT
AS
BEGIN
    DECLARE @v_balance DECIMAL(10,2);

    BEGIN TRY
        BEGIN TRANSACTION;

        SELECT @v_balance = balance
        FROM accounts WITH (UPDLOCK)
        WHERE id = @p_from_id;

        IF @v_balance IS NULL
            RAISERROR('Account not found', 16, 1);

        IF @v_balance < @p_amount
            RAISERROR('Insufficient funds', 16, 1);

        UPDATE accounts SET balance = balance - @p_amount WHERE id = @p_from_id;
        UPDATE accounts SET balance = balance + @p_amount WHERE id = @p_to_id;

        COMMIT TRANSACTION;
        SET @p_status = 'SUCCESS';

    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        SET @p_status = CONCAT('ERROR: ', ERROR_MESSAGE());
    END CATCH
END;

Error Functions (CATCH Block Only)

Function	Returns
`ERROR_NUMBER()`	Error number
`ERROR_MESSAGE()`	Error message text
`ERROR_SEVERITY()`	Severity level (0-25)
`ERROR_STATE()`	Error state number
`ERROR_LINE()`	Line number of the error
`ERROR_PROCEDURE()`	Procedure name where error occurred

Raising Custom Errors

-- Classic syntax
RAISERROR('Something went wrong: %s', 16, 1, @details);

-- Modern syntax (SQL Server 2012+)
THROW 50001, 'Custom error message', 1;

-- Re-throw in a CATCH block (preserves original error)
BEGIN CATCH
    THROW;  -- Re-raises the original error exactly
END CATCH

Nested TRY/CATCH

BEGIN TRY
    BEGIN TRY
        INSERT INTO employees (id, name) VALUES (1, 'Duplicate');
    END TRY
    BEGIN CATCH
        PRINT 'Inner error handled: ' + ERROR_MESSAGE();
    END CATCH

    PRINT 'Outer code continues after inner error';

END TRY
BEGIN CATCH
    PRINT 'Outer error: ' + ERROR_MESSAGE();
END CATCH

Error Handling Comparison

Feature	PostgreSQL	MySQL	SQL Server
Syntax	`EXCEPTION WHEN...THEN`	`DECLARE HANDLER FOR...`	`BEGIN TRY...BEGIN CATCH`
Placement	End of `BEGIN...END`	Start of `BEGIN...END`	Anywhere
Specific errors	Named conditions	Error numbers or SQLSTATE	`ERROR_NUMBER()` function
Catch-all	`WHEN OTHERS`	`FOR SQLEXCEPTION`	`BEGIN CATCH`
Auto-rollback	Yes (implicit savepoint)	No (must `ROLLBACK`)	No (must `ROLLBACK`)
Re-throw	`RAISE;`	Not directly supported	`THROW;`
Nesting	Nested `BEGIN...EXCEPTION...END`	Multiple handlers	Nested `TRY...CATCH`

Comprehensive Practical Example

Let's combine variables, IF/ELSE, WHILE, and error handling into a realistic batch processing procedure:

PostgreSQL

CREATE OR REPLACE PROCEDURE process_department_review(
    IN p_department VARCHAR(50),
    INOUT p_summary TEXT DEFAULT ''
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_emp        RECORD;
    v_raise_pct  DECIMAL(5,2);
    v_new_salary DECIMAL(10,2);
    v_tenure     INT;
    v_processed  INT := 0;
    v_skipped    INT := 0;
    v_total_cost DECIMAL(10,2) := 0;
BEGIN
    -- Validate department
    IF NOT EXISTS (SELECT 1 FROM employees WHERE department = p_department) THEN
        p_summary := 'ERROR: Department "' || p_department || '" not found';
        RETURN;
    END IF;

    FOR v_emp IN
        SELECT id, name, salary, hire_date, is_active
        FROM employees WHERE department = p_department ORDER BY hire_date
    LOOP
        -- Each employee gets its own error handling
        BEGIN
            IF NOT v_emp.is_active THEN
                v_skipped := v_skipped + 1;
                CONTINUE;
            END IF;

            v_tenure := EXTRACT(YEAR FROM AGE(CURRENT_DATE, v_emp.hire_date));

            IF v_tenure < 1 THEN
                v_skipped := v_skipped + 1;
                CONTINUE;
            END IF;

            IF v_tenure >= 5 THEN
                v_raise_pct := 8.0;
            ELSIF v_tenure >= 3 THEN
                v_raise_pct := 5.0;
            ELSE
                v_raise_pct := 3.0;
            END IF;

            v_new_salary := ROUND(v_emp.salary * (1 + v_raise_pct / 100), 2);

            UPDATE employees SET salary = v_new_salary WHERE id = v_emp.id;

            INSERT INTO salary_adjustments
                (employee_id, old_salary, new_salary, reason, adjusted_at)
            VALUES
                (v_emp.id, v_emp.salary, v_new_salary, 'dept_review', NOW());

            v_total_cost := v_total_cost + (v_new_salary - v_emp.salary);
            v_processed := v_processed + 1;

            RAISE NOTICE '% (% yrs): % → % (+%%%)',
                v_emp.name, v_tenure, v_emp.salary, v_new_salary, v_raise_pct;

        EXCEPTION
            WHEN OTHERS THEN
                RAISE NOTICE 'Error processing %: %', v_emp.name, SQLERRM;
                v_skipped := v_skipped + 1;
        END;
    END LOOP;

    p_summary := FORMAT(
        'Dept: %s | Processed: %s | Skipped: %s | Cost: $%s',
        p_department, v_processed, v_skipped, v_total_cost
    );
END;
$$;

CALL process_department_review('Engineering', NULL);

NOTICE:  Alice (6 yrs): 95000.00 → 102600.00 (+8.00%)
NOTICE:  Bob (4 yrs): 88000.00 → 92400.00 (+5.00%)

p_summary
Dept: Engineering \| Processed: 2 \| Skipped: 0 \| Cost: $12000.00

This example demonstrates every concept covered:

Variables: counters, accumulators, RECORD type
IF/ELSE: tenure-based raise percentage, inactive employee skipping
FOR loop: iterating over query results
CONTINUE: skipping ineligible employees
Nested EXCEPTION: per-employee error handling without aborting the batch

Common Mistakes to Avoid

Mistake 1: Uninitialized Variables

-- WRONG: v_total is NULL by default; NULL + anything = NULL
DECLARE v_total DECIMAL(10,2);
v_total := v_total + 100;  -- NULL + 100 = NULL!

-- CORRECT: Initialize to 0
DECLARE v_total DECIMAL(10,2) := 0;
v_total := v_total + 100;  -- 0 + 100 = 100

Mistake 2: Infinite Loops

-- WRONG: Counter never incremented
DECLARE v_i INT := 1;
WHILE v_i <= 10 LOOP
    RAISE NOTICE '%', v_i;
    -- Forgot: v_i := v_i + 1; → runs forever!
END LOOP;

-- CORRECT
WHILE v_i <= 10 LOOP
    RAISE NOTICE '%', v_i;
    v_i := v_i + 1;
END LOOP;

Mistake 3: Swallowing Errors Silently

-- WRONG: Error vanishes without trace
EXCEPTION
    WHEN OTHERS THEN
        NULL;  -- Silently ignores everything

-- CORRECT: At minimum log the error
EXCEPTION
    WHEN OTHERS THEN
        INSERT INTO error_log (message, occurred_at)
        VALUES (SQLERRM, NOW());
        RAISE NOTICE 'Error logged: %', SQLERRM;

Mistake 4: Missing Transaction Rollback in Handlers

-- WRONG (MySQL): Partial changes may persist
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    SET p_status = 'Error occurred';
END;

-- CORRECT
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    SET p_status = 'Error occurred';
END;

Mistake 5: SQL Server IF Without BEGIN/END

-- WRONG: Only the first line is conditional
IF @count > 0
    SET @status = 'Found';
    INSERT INTO audit_log (action) VALUES ('Found records');  -- Always runs!

-- CORRECT
IF @count > 0
BEGIN
    SET @status = 'Found';
    INSERT INTO audit_log (action) VALUES ('Found records');
END

Summary

SQL variables control flow and error handling are the building blocks of procedural database programming:

Variables store intermediate values. PostgreSQL uses := for assignment, MySQL uses SET =, and SQL Server uses SET @var =. Always initialize variables before arithmetic to avoid NULL propagation.
IF/ELSE enables conditional branching. Watch the syntax differences: PostgreSQL uses ELSIF, MySQL uses ELSEIF, and SQL Server uses ELSE IF with required BEGIN/END blocks for multiple statements.
WHILE loops repeat code while a condition is true. Use EXIT/LEAVE/BREAK to terminate early and CONTINUE/ITERATE to skip iterations. PostgreSQL also offers FOR loops over ranges and query results. Prefer set-based operations over cursors when possible.
Error handling prevents crashes and data corruption. PostgreSQL uses EXCEPTION blocks with automatic savepoint rollback. MySQL uses DECLARE HANDLER with explicit ROLLBACK. SQL Server uses TRY/CATCH with rich ERROR_*() functions.
Always log errors rather than silently swallowing them, ensure loops can terminate, and explicitly rollback transactions in error handlers (MySQL and SQL Server).