Part 8B: Encryption, Binary, Prepared Statements, Cursors & Error Handling

Section E — Encryption & Hashing
Section F — Bit & Binary Functions
Section G — Prepared Statements
Section H — Cursors
Section I — Error Handling

Encryption & Hashing

E.1 — MD5(string)

What it does: Takes any text and turns it into a fixed 32-character hexadecimal "fingerprint." Even a tiny change in the input produces a completely different hash. It is a one-way function — you cannot reverse it to get the original text back.

SQL

SELECT
  MD5('hello')        AS hash_hello,
  MD5('Hello')        AS hash_Hello,
  MD5('password123') AS hash_password;

hash_hello	hash_Hello	hash_password
5d41402abc4b2a76b9719d911017c592	8b1a9953c4611296a827abf8c47804d7	482c811da5d5b4bc6d497ffa98491e38

Note: MD5 produces a 32-character hex string (128 bits). It is considered cryptographically weak for security — use SHA2 for passwords or sensitive data.

E.2 — SHA1(string)

What it does: Similar to MD5 but produces a longer 40-character hex output (160 bits). Slightly more secure than MD5 but still not recommended for high-security needs.

SQL

SELECT
  SHA1('hello')        AS sha1_hello,
  SHA1('password123') AS sha1_password;

sha1_hello	sha1_password
aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d	cbfdac6008f9cab4083784cbd1874f76618d2a97

E.3 — SHA2(string, hash_length)

What it does: The strongest hashing function available in MySQL. The second parameter sets the output bit length: 224, 256, 384, or 512. SHA-256 (64 hex characters) is the most commonly used.

SQL

SELECT
  SHA2('hello', 256) AS sha256_hello,
  SHA2('hello', 512) AS sha512_hello;

sha256_hello
2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824

sha512_hello
9b71d224bd62f3785d96d46ad3ea3d73319bfbc2890caadae2dff72519673ca7 2323c3d99ba5c11d7c7acc6e14b8c5da0c4663475c2e5c3adef46f73bcdec043

SHA2 Bit Lengths

SHA2(str, 224) = 56 hex chars • SHA2(str, 256) = 64 hex chars • SHA2(str, 384) = 96 hex chars • SHA2(str, 512) = 128 hex chars

E.4 — AES_ENCRYPT(data, key)

What it does: Encrypts data using the AES (Advanced Encryption Standard) algorithm with a secret key. Unlike hashing, encryption is reversible — you can get the original data back with the same key. The output is raw binary, so we wrap it in HEX() to display it.

SQL

SELECT
  HEX(AES_ENCRYPT('Secret Order #42', 'my-secret-key-16')) AS encrypted_hex;

encrypted_hex
A3F8B2D1E47C9A0856F1D3E2B7C4A59012E8F6D3A1B7C4E5

Note: The AES key should be exactly 16, 24, or 32 bytes long for AES-128, AES-192, or AES-256 respectively. The actual hex output will vary between MySQL installations.

E.5 — AES_DECRYPT(encrypted, key)

What it does: Decrypts data that was previously encrypted with AES_ENCRYPT, using the same key. This is the "roundtrip" — encrypt then decrypt to get the original value back.

SQL — Full Roundtrip: Encrypt then Decrypt

-- Step 1: Encrypt a customer credit note
SET @secret_key = 'my-secret-key-16';
SET @original   = 'Customer: Alice, Credit: $500';
SET @encrypted  = AES_ENCRYPT(@original, @secret_key);

-- Step 2: View encrypted (as hex) and decrypted
SELECT
  @original                                           AS original_text,
  HEX(@encrypted)                                    AS encrypted_hex,
  CAST(AES_DECRYPT(@encrypted, @secret_key) AS CHAR) AS decrypted_text;

original_text	encrypted_hex	decrypted_text
Customer: Alice, Credit: $500	7A4F2E8B1D3C6A9F...82B1E4	Customer: Alice, Credit: $500

Key Takeaway

The decrypted text matches the original exactly. If you use the wrong key, AES_DECRYPT returns NULL.

E.6 — RANDOM_BYTES(length)

What it does: Generates a string of cryptographically secure random bytes. The parameter specifies how many bytes you want. Since the output is binary, we use HEX() to display it as readable hex characters.

SQL

SELECT
  HEX(RANDOM_BYTES(16)) AS random_16_bytes,
  HEX(RANDOM_BYTES(8))  AS random_8_bytes;

random_16_bytes	random_8_bytes
A7F3B2C1D8E64F0912AB34CD56EF7890	3E8A1B7C4D2F6E09

Note: Every time you run this, you get different output. This is great for generating unique tokens, salt values for hashing, or random encryption keys.

E.7 — HEX(string)

What it does: Converts each character in a string to its hexadecimal byte representation. For example, the letter 'A' is 0x41 in hex. This is useful for inspecting binary data or encoding strings for safe transport.

SQL

SELECT
  HEX('ABC')     AS hex_abc,
  HEX('Hello')   AS hex_hello,
  HEX('MySQL')   AS hex_mysql;

hex_abc	hex_hello	hex_mysql
414243	48656C6C6F	4D7953514C

E.8 — UNHEX(hex_string)

What it does: The reverse of HEX() — takes a hexadecimal string and converts it back to the original characters. Think of it as "decoding" a hex-encoded message.

SQL

SELECT
  UNHEX('414243')     AS from_hex_1,
  UNHEX('48656C6C6F') AS from_hex_2,
  UNHEX('4D7953514C') AS from_hex_3;

from_hex_1	from_hex_2	from_hex_3
ABC	Hello	MySQL

Encryption & Hashing Summary

MD5 / SHA1 / SHA2 — One-way hashing. You cannot reverse them. Use SHA2(str, 256) or higher for security.
AES_ENCRYPT / AES_DECRYPT — Two-way encryption. Same key encrypts and decrypts.
RANDOM_BYTES — Generate secure random data for tokens or keys.
HEX / UNHEX — Convert between text and its hex representation.

Bit & Binary Functions

F.1 — BIN(number)

What it does: Converts a decimal number to its binary (base-2) string representation. Computers think in binary (0s and 1s), so this lets you see how a number looks "under the hood."

SQL

SELECT
  BIN(0)  AS bin_0,
  BIN(5)  AS bin_5,
  BIN(10) AS bin_10,
  BIN(15) AS bin_15,
  BIN(255)AS bin_255;

bin_0	bin_5	bin_10	bin_15	bin_255
0	101	1010	1111	11111111

Visual: How 10 looks in binary

8 + 0 + 2 + 0 = 10

F.2 — OCT(number)

What it does: Converts a decimal number to its octal (base-8) string. Octal uses digits 0–7 and was historically used in Unix file permissions.

SQL

SELECT
  OCT(8)   AS oct_8,
  OCT(10)  AS oct_10,
  OCT(64)  AS oct_64,
  OCT(255) AS oct_255;

oct_8	oct_10	oct_64	oct_255
10	12	100	377

F.3 — HEX(number)

What it does: Converts a decimal number to its hexadecimal (base-16) string. Hex uses digits 0–9 plus letters A–F. It is widely used in colors (#FF0000), memory addresses, and more.

SQL

SELECT
  HEX(10)  AS hex_10,
  HEX(255) AS hex_255,
  HEX(256) AS hex_256,
  HEX(4096)AS hex_4096;

hex_10	hex_255	hex_256	hex_4096
A	FF	100	1000

F.4 — CONV(number, from_base, to_base)

What it does: The universal base converter. Converts a number from any base (2–36) to any other base. Think of it as a translator between number systems.

SQL

SELECT
  CONV('1010', 2, 10)  AS binary_to_decimal,
  CONV('FF',   16, 10) AS hex_to_decimal,
  CONV(255,    10, 16) AS decimal_to_hex,
  CONV(255,    10, 2)  AS decimal_to_binary,
  CONV('1010', 2, 16)  AS binary_to_hex;

binary_to_decimal	hex_to_decimal	decimal_to_hex	decimal_to_binary	binary_to_hex
10	255	FF	11111111	A

F.5 — BIT_COUNT(number)

What it does: Counts how many 1-bits are in the binary representation of a number. For example, 10 in binary is 1010, which has two 1-bits.

SQL

SELECT
  BIT_COUNT(0)   AS bits_0,    -- binary: 0       = 0 ones
  BIT_COUNT(5)   AS bits_5,    -- binary: 101     = 2 ones
  BIT_COUNT(10)  AS bits_10,   -- binary: 1010    = 2 ones
  BIT_COUNT(15)  AS bits_15,   -- binary: 1111    = 4 ones
  BIT_COUNT(255) AS bits_255;  -- binary: 11111111 = 8 ones

bits_0	bits_5	bits_10	bits_15	bits_255
0	2	2	4	8

F.6 — & (Bitwise AND)

What it does: Compares each bit of two numbers. The result bit is 1 only if both bits are 1. Think of it as "both must agree."

SQL

-- 12 in binary: 1100
-- 10 in binary: 1010
-- AND result:    1000 = 8
SELECT
  12 & 10         AS and_result,
  BIN(12)          AS bin_12,
  BIN(10)          AS bin_10,
  BIN(12 & 10)    AS bin_result;

and_result	bin_12	bin_10	bin_result
8	1100	1010	1000

F.7 — | (Bitwise OR)

What it does: Compares each bit of two numbers. The result bit is 1 if either bit (or both) is 1. Think of it as "at least one says yes."

SQL

-- 12 in binary: 1100
-- 10 in binary: 1010
-- OR result:    1110 = 14
SELECT
  12 | 10         AS or_result,
  BIN(12)          AS bin_12,
  BIN(10)          AS bin_10,
  BIN(12 | 10)    AS bin_result;

or_result	bin_12	bin_10	bin_result
14	1100	1010	1110

F.8 — ^ (Bitwise XOR)

What it does: Compares each bit of two numbers. The result bit is 1 only if the bits are different. If both are the same (both 0 or both 1), the result is 0. Think of it as "exactly one says yes."

SQL

-- 12 in binary: 1100
-- 10 in binary: 1010
-- XOR result:   0110 = 6
SELECT
  12 ^ 10         AS xor_result,
  BIN(12)          AS bin_12,
  BIN(10)          AS bin_10,
  BIN(12 ^ 10)    AS bin_result;

xor_result	bin_12	bin_10	bin_result
6	1100	1010	110

Visual Truth Table — AND / OR / XOR Bit-by-Bit

This table shows every possible combination of two input bits (A and B) and what each operator produces:

A	B	A AND B	A OR B	A XOR B
0	0	0	0	0
0	1	0	1	1
1	0	0	1	1
1	1	1	1	0

Bit-by-Bit Walkthrough: 12 & 10, 12 | 10, 12 ^ 10

Bit Position	8 (2³)	4 (2²)	2 (2¹)	Decimal
12	1	1	0	12
10	1	0	1	10
AND (&)	1	0	0	8
OR (\|)	1	1	1	14
XOR (^)	0	1	1	6

F.9 — ~ (Bitwise NOT)

What it does: Flips every bit — all 0s become 1s and all 1s become 0s. MySQL uses 64-bit unsigned integers, so ~0 gives the maximum 64-bit value.

SQL

SELECT
  ~0                        AS not_zero,
  ~1                        AS not_one,
  BIN(~5 & 0xFF)          AS not_5_low8bits;
-- We mask with 0xFF to see just the low 8 bits

not_zero	not_one	not_5_low8bits
18446744073709551615	18446744073709551614	11111010

Note: ~5 in the low 8 bits is 11111010 (250), because 5 = 00000101, and flipping gives 11111010. MySQL uses 64-bit values so the full result is very large.

F.10 — << (Left Shift)

What it does: Shifts all bits to the left by the specified number of positions. Each left shift doubles the number (just like multiplying by 2).

SQL

SELECT
  1  << 0 AS shift_0,  -- 1  (no shift)
  1  << 1 AS shift_1,  -- 2  (doubled once)
  1  << 2 AS shift_2,  -- 4  (doubled twice)
  1  << 3 AS shift_3,  -- 8  (doubled 3x)
  5  << 1 AS five_x2,  -- 10 (5 * 2)
  5  << 2 AS five_x4;  -- 20 (5 * 4)

shift_0	shift_1	shift_2	shift_3	five_x2	five_x4
1	2	4	8	10	20

F.11 — >> (Right Shift)

What it does: Shifts all bits to the right by the specified number of positions. Each right shift halves the number (integer division by 2, dropping any remainder).

SQL

SELECT
  16 >> 0 AS shift_0,  -- 16 (no shift)
  16 >> 1 AS shift_1,  -- 8  (halved once)
  16 >> 2 AS shift_2,  -- 4  (halved twice)
  16 >> 3 AS shift_3,  -- 2  (halved 3x)
  16 >> 4 AS shift_4,  -- 1  (halved 4x)
  15 >> 1 AS odd_half; -- 7  (15/2 = 7, drops .5)

shift_0	shift_1	shift_2	shift_3	shift_4	odd_half
16	8	4	2	1	7

Bit & Binary Summary

BIN / OCT / HEX — Convert numbers between base systems for display.
CONV() — Universal converter between any base (2-36).
BIT_COUNT() — Count the 1-bits in a number.
& | ^ ~ — Bitwise AND, OR, XOR, NOT (operate on individual bits).
<< >> — Left/right shift (multiply/divide by powers of 2).

Prepared Statements

What are Prepared Statements? A prepared statement is like a template query with placeholders (?). You write the query once, then MySQL "prepares" (compiles) it. After that you can run it many times with different values — faster and safer than building a new query each time.

G.1 — PREPARE

What it does: Compiles a SQL template and saves it under a name. The ? marks are placeholders for values you will provide later.

SQL

PREPARE stmt_products FROM
  'SELECT id, name, category, price FROM products WHERE price > ?';

Status
Statement prepared

G.2 — SET @variable

What it does: Creates a user-defined session variable and assigns a value to it. This variable will be plugged into the prepared statement's placeholder.

SQL

SET @min_price = 100;

Status
Query OK, 0 rows affected

G.3 — EXECUTE ... USING

What it does: Runs the prepared statement, replacing each ? placeholder with the variable(s) you supply after USING.

SQL

EXECUTE stmt_products USING @min_price;

id	name	category	price
3	Wireless Headphones	Electronics	149.99
5	Standing Desk	Furniture	349.00
7	4K Monitor	Electronics	289.99
9	Ergonomic Chair	Furniture	199.50

G.4 — DEALLOCATE PREPARE

What it does: Frees the memory used by the prepared statement. Always clean up when you are done to avoid resource leaks.

SQL

DEALLOCATE PREPARE stmt_products;

Status
Query OK, 0 rows affected

G.5 — Full Practical Example: Prepare Once, Execute 3 Times

Scenario: We have a products table and want to search by category using the same prepared statement three times with different values.

Sample Data — products table

CREATE TABLE products (
  id       INT PRIMARY KEY AUTO_INCREMENT,
  name     VARCHAR(100),
  category VARCHAR(50),
  price    DECIMAL(10,2),
  stock    INT
);

INSERT INTO products (name, category, price, stock) VALUES
  ('Notebook',           'Stationery',   4.99,   200),
  ('Ballpoint Pen 10-pk', 'Stationery',   7.50,   350),
  ('Wireless Headphones', 'Electronics',  149.99, 45),
  ('USB-C Cable',         'Electronics',  12.99, 500),
  ('Standing Desk',       'Furniture',    349.00, 15),
  ('Desk Lamp',           'Furniture',    39.99, 80),
  ('4K Monitor',          'Electronics',  289.99, 30),
  ('Sticky Notes',        'Stationery',   3.25,  600),
  ('Ergonomic Chair',     'Furniture',    199.50, 22),
  ('Webcam HD',           'Electronics',  59.99, 75);

SQL — Prepare once, execute 3 times

-- Step 1: Prepare the template
PREPARE find_by_category FROM
  'SELECT id, name, price, stock FROM products WHERE category = ?';

-- Step 2: Execute with 'Electronics'
SET @cat = 'Electronics';
EXECUTE find_by_category USING @cat;

-- Step 3: Execute with 'Furniture'
SET @cat = 'Furniture';
EXECUTE find_by_category USING @cat;

-- Step 4: Execute with 'Stationery'
SET @cat = 'Stationery';
EXECUTE find_by_category USING @cat;

-- Step 5: Clean up
DEALLOCATE PREPARE find_by_category;

Execution 1 — category = 'Electronics':

id	name	price	stock
3	Wireless Headphones	149.99	45
4	USB-C Cable	12.99	500
7	4K Monitor	289.99	30
10	Webcam HD	59.99	75

Execution 2 — category = 'Furniture':

id	name	price	stock
5	Standing Desk	349.00	15
6	Desk Lamp	39.99	80
9	Ergonomic Chair	199.50	22

Execution 3 — category = 'Stationery':

id	name	price	stock
1	Notebook	4.99	200
2	Ballpoint Pen 10-pk	7.50	350
8	Sticky Notes	3.25	600

Why Use Prepared Statements?

Performance — MySQL parses and optimizes the query only once, then reuses the plan.
Security — Parameters are sent separately from the SQL, preventing SQL injection attacks.
Cleanliness — No messy string concatenation to build queries.

Cursors

What are Cursors? Normally SQL works with entire sets of rows at once. A cursor lets you process rows one at a time, like reading a book line by line. Cursors only work inside stored procedures or functions.

H.1 — DECLARE cursor_name CURSOR FOR SELECT

What it does: Creates a cursor and links it to a SELECT query. This does not run the query yet — it just defines which rows the cursor will iterate through.

SQL

DECLARE emp_cursor CURSOR FOR
  SELECT id, name, salary FROM employees;

H.2 — DECLARE CONTINUE HANDLER FOR NOT FOUND

What it does: Tells MySQL what to do when the cursor runs out of rows. Without this handler, MySQL would throw an error after the last row. We set a flag variable so the loop knows when to stop.

SQL

DECLARE done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

H.3 — OPEN cursor_name

What it does: Executes the cursor's SELECT query and prepares the first row for reading. Think of it as opening a book to page one.

SQL

OPEN emp_cursor;

H.4 — FETCH cursor_name INTO var1, var2, ...

What it does: Reads the next row from the cursor and stores each column value into local variables. Each FETCH advances to the next row automatically.

SQL

FETCH emp_cursor INTO v_id, v_name, v_salary;

H.5 — CLOSE cursor_name

What it does: Closes the cursor and releases its resources. Always close cursors when you are done to free up memory.

SQL

CLOSE emp_cursor;

H.6 — Full Practical Example: Salary-Tier Bonus Calculator

Scenario: We want a stored procedure that loops through every employee, calculates a bonus based on their salary tier, and inserts the result into a bonus_log table.

Bonus rules: Salary under $50,000 = 15% bonus. Salary $50,000–$79,999 = 10% bonus. Salary $80,000+ = 5% bonus.

Setup — employees table (before)

CREATE TABLE employees (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  name       VARCHAR(100),
  department VARCHAR(50),
  salary     DECIMAL(10,2),
  hire_date  DATE
);

INSERT INTO employees (name, department, salary, hire_date) VALUES
  ('Alice Johnson',  'Engineering', 92000.00, '2020-03-15'),
  ('Bob Smith',      'Marketing',   55000.00, '2021-07-01'),
  ('Carol Davis',    'Engineering', 78000.00, '2019-11-20'),
  ('Dave Wilson',    'Sales',       42000.00, '2022-01-10'),
  ('Eve Martinez',   'Marketing',   61000.00, '2021-09-05'),
  ('Frank Brown',    'Sales',       38000.00, '2023-04-12');

CREATE TABLE bonus_log (
  id          INT PRIMARY KEY AUTO_INCREMENT,
  employee_id INT,
  emp_name    VARCHAR(100),
  salary      DECIMAL(10,2),
  tier        VARCHAR(20),
  bonus_pct   DECIMAL(4,2),
  bonus_amt   DECIMAL(10,2),
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Employees table (before):

id	name	department	salary	hire_date
1	Alice Johnson	Engineering	92000.00	2020-03-15
2	Bob Smith	Marketing	55000.00	2021-07-01
3	Carol Davis	Engineering	78000.00	2019-11-20
4	Dave Wilson	Sales	42000.00	2022-01-10
5	Eve Martinez	Marketing	61000.00	2021-09-05
6	Frank Brown	Sales	38000.00	2023-04-12

SQL — Stored Procedure with Cursor

DELIMITER //

CREATE PROCEDURE calculate_bonuses()
BEGIN
    -- Local variables (must be declared before cursor)
    DECLARE v_id     INT;
    DECLARE v_name   VARCHAR(100);
    DECLARE v_salary DECIMAL(10,2);
    DECLARE v_tier   VARCHAR(20);
    DECLARE v_pct    DECIMAL(4,2);
    DECLARE v_bonus  DECIMAL(10,2);
    DECLARE done     INT DEFAULT 0;

    -- Declare cursor
    DECLARE emp_cursor CURSOR FOR
        SELECT id, name, salary FROM employees;

    -- Handler: set done=1 when no more rows
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- Clear previous bonus log
    TRUNCATE TABLE bonus_log;

    -- Open the cursor (executes the SELECT)
    OPEN emp_cursor;

    -- Loop through all rows
    read_loop: LOOP
        FETCH emp_cursor INTO v_id, v_name, v_salary;

        -- Exit loop when no more rows
        IF done = 1 THEN
            LEAVE read_loop;
        END IF;

        -- Determine bonus tier
        IF v_salary < 50000 THEN
            SET v_tier = 'Junior';
            SET v_pct  = 0.15;
        ELSEIF v_salary < 80000 THEN
            SET v_tier = 'Mid-Level';
            SET v_pct  = 0.10;
        ELSE
            SET v_tier = 'Senior';
            SET v_pct  = 0.05;
        END IF;

        -- Calculate bonus amount
        SET v_bonus = v_salary * v_pct;

        -- Insert into bonus log
        INSERT INTO bonus_log (employee_id, emp_name, salary, tier, bonus_pct, bonus_amt)
        VALUES (v_id, v_name, v_salary, v_tier, v_pct * 100, v_bonus);

    END LOOP;

    -- Close the cursor
    CLOSE emp_cursor;

    -- Return the results
    SELECT * FROM bonus_log ORDER BY employee_id;
END //

DELIMITER ;

-- Run it!
CALL calculate_bonuses();

bonus_log table (after running the procedure):

id	employee_id	emp_name	salary	tier	bonus_pct	bonus_amt
1	1	Alice Johnson	92000.00	Senior	5.00	4600.00
2	2	Bob Smith	55000.00	Mid-Level	10.00	5500.00
3	3	Carol Davis	78000.00	Mid-Level	10.00	7800.00
4	4	Dave Wilson	42000.00	Junior	15.00	6300.00
5	5	Eve Martinez	61000.00	Mid-Level	10.00	6100.00
6	6	Frank Brown	38000.00	Junior	15.00	5700.00

How the Cursor Works Step by Step

1. DECLARE — defines cursor + variables. 2. OPEN — runs the SELECT. 3. FETCH in a LOOP — reads one row at a time. 4. Process each row (calculate bonus, insert log). 5. NOT FOUND handler sets done=1 when out of rows. 6. CLOSE — releases the cursor.

Error Handling

Why Error Handling? Real-world stored procedures can encounter problems: duplicate keys, missing tables, null values, and more. Error handlers let you catch these problems gracefully instead of crashing. Think of it like a try/catch block in other programming languages.

I.1 — DECLARE ... HANDLER FOR SQLEXCEPTION

What it does: Catches any SQL error (like a universal catch-all). When an error happens, the handler runs its code block instead of crashing the procedure.

SQL

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    -- This runs whenever ANY SQL error occurs
    SET @error_occurred = 1;
    SET @error_msg = 'A SQL error occurred';
END;

CONTINUE vs EXIT: A CONTINUE handler lets the procedure keep running after the error. An EXIT handler immediately ends the current BEGIN...END block.

I.2 — DECLARE ... HANDLER FOR SQLWARNING

What it does: Catches SQL warnings — non-fatal issues like data truncation or dividing by zero (which returns NULL with a warning). Useful for logging potential problems.

SQL

DECLARE CONTINUE HANDLER FOR SQLWARNING
BEGIN
    SET @warning_count = @warning_count + 1;
END;

I.3 — DECLARE ... HANDLER FOR NOT FOUND

What it does: Fires when a SELECT INTO or cursor FETCH finds no rows. This is the same handler we used with cursors — it tells us when there is nothing left to read.

SQL

DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
    SET @no_data = 1;
END;

I.4 — DECLARE ... HANDLER FOR 1062 (Specific Error Code)

What it does: Catches a specific MySQL error by its number. Error 1062 is "Duplicate entry" — it fires when you try to insert a row that violates a UNIQUE constraint.

SQL

-- Catch duplicate key error specifically
DECLARE CONTINUE HANDLER FOR 1062
BEGIN
    SET @is_duplicate = 1;
    SET @error_msg = 'Duplicate entry detected';
END;

Common MySQL Error Codes

1062 = Duplicate entry • 1048 = Column cannot be NULL • 1054 = Unknown column • 1146 = Table doesn't exist • 1452 = Foreign key constraint fails

I.5 — SIGNAL SQLSTATE — Raise a Custom Error

What it does: Lets you throw your own custom error, just like "throw new Error()" in JavaScript. You specify a SQLSTATE code and a custom message. This is great for business rule validation.

SQL

DELIMITER //
CREATE PROCEDURE validate_order_qty(IN qty INT)
BEGIN
    IF qty <= 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Order quantity must be greater than zero',
                MYSQL_ERRNO  = 5001;
    END IF;

    SELECT CONCAT('Order placed for ', qty, ' items') AS result;
END //
DELIMITER ;

-- Test with valid value
CALL validate_order_qty(5);

-- Test with invalid value
CALL validate_order_qty(-3);

Result of CALL validate_order_qty(5):

result
Order placed for 5 items

Result of CALL validate_order_qty(-3):

ERROR 5001 (45000): Order quantity must be greater than zero

Note: SQLSTATE '45000' is the standard code for user-defined errors. The MYSQL_ERRNO is optional and lets you assign a custom numeric code.

I.6 — RESIGNAL — Re-Raise a Caught Error

What it does: After catching an error with a handler, RESIGNAL re-throws it so the caller also gets the error. Useful when you want to log the error but still let it propagate up.

SQL

DELIMITER //
CREATE PROCEDURE safe_insert_product(
    IN p_name VARCHAR(100),
    IN p_price DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- Log the error (you could insert into an error_log table)
        SELECT 'Error caught and logged, re-raising...' AS status;

        -- Re-raise the original error to the caller
        RESIGNAL;
    END;

    INSERT INTO products (name, category, price, stock)
    VALUES (p_name, 'General', p_price, 0);
END //
DELIMITER ;

I.7 — GET DIAGNOSTICS — Get Error Details

What it does: After an error is caught by a handler, GET DIAGNOSTICS lets you retrieve the full error details: the SQLSTATE code, error number, and message text. Like reading the full crash report.

SQL

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS CONDITION 1
        @err_state = RETURNED_SQLSTATE,
        @err_num   = MYSQL_ERRNO,
        @err_msg   = MESSAGE_TEXT;
END;

-- After an error is caught, you can inspect:
SELECT @err_state AS sqlstate_code,
       @err_num   AS error_number,
       @err_msg   AS error_message;

sqlstate_code	error_number	error_message
23000	1062	Duplicate entry 'Notebook' for key 'products.name'

I.8 — Full Practical Example: Insert with Error Handling

Scenario: A procedure that tries to insert a new product. If the product name already exists (duplicate key), it catches the error, logs it to an error_log table, and returns a status message instead of crashing.

Setup — Tables

-- Products table with unique name constraint
CREATE TABLE products (
  id       INT PRIMARY KEY AUTO_INCREMENT,
  name     VARCHAR(100) UNIQUE,
  category VARCHAR(50),
  price    DECIMAL(10,2),
  stock    INT
);

-- Error log table
CREATE TABLE error_log (
  id          INT PRIMARY KEY AUTO_INCREMENT,
  proc_name   VARCHAR(100),
  err_code    INT,
  err_state   VARCHAR(10),
  err_message VARCHAR(500),
  input_data  VARCHAR(500),
  logged_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert initial data
INSERT INTO products (name, category, price, stock) VALUES
  ('Notebook',    'Stationery',  4.99,  200),
  ('USB-C Cable', 'Electronics', 12.99, 500);

SQL — Procedure with Full Error Handling

DELIMITER //

CREATE PROCEDURE add_product(
    IN p_name     VARCHAR(100),
    IN p_category VARCHAR(50),
    IN p_price    DECIMAL(10,2),
    IN p_stock    INT
)
BEGIN
    -- Variables for error details
    DECLARE v_err_code    INT DEFAULT 0;
    DECLARE v_err_state   VARCHAR(10) DEFAULT '';
    DECLARE v_err_msg     VARCHAR(500) DEFAULT '';
    DECLARE v_has_error   INT DEFAULT 0;

    -- Handler: catch ANY SQL exception
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        SET v_has_error = 1;

        -- Get the error details
        GET DIAGNOSTICS CONDITION 1
            v_err_state = RETURNED_SQLSTATE,
            v_err_code  = MYSQL_ERRNO,
            v_err_msg   = MESSAGE_TEXT;

        -- Log the error
        INSERT INTO error_log (proc_name, err_code, err_state, err_message, input_data)
        VALUES (
            'add_product',
            v_err_code,
            v_err_state,
            v_err_msg,
            CONCAT('name=', p_name, ', cat=', p_category,
                   ', price=', p_price, ', stock=', p_stock)
        );
    END;

    -- Business rule: price must be positive
    IF p_price <= 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Price must be greater than zero';
    END IF;

    -- Attempt the insert
    INSERT INTO products (name, category, price, stock)
    VALUES (p_name, p_category, p_price, p_stock);

    -- Return status
    IF v_has_error = 1 THEN
        SELECT
            'FAILED'    AS status,
            v_err_code  AS error_code,
            v_err_msg   AS error_message;
    ELSE
        SELECT
            'SUCCESS'         AS status,
            LAST_INSERT_ID()  AS new_product_id,
            p_name            AS product_name;
    END IF;
END //

DELIMITER ;

Test 1 — Successful insert (new product)

CALL add_product('Wireless Mouse', 'Electronics', 29.99, 150);

status	new_product_id	product_name
SUCCESS	3	Wireless Mouse

Test 2 — Duplicate name (error caught)

CALL add_product('Notebook', 'Stationery', 5.99, 100);

status	error_code	error_message
FAILED	1062	Duplicate entry 'Notebook' for key 'products.name'

Test 3 — Invalid price (custom SIGNAL error)

CALL add_product('Free Item', 'Promo', -5.00, 10);

status	error_code	error_message
FAILED	1644	Price must be greater than zero

Check the error_log table

SELECT * FROM error_log ORDER BY id;

id	proc_name	err_code	err_state	err_message	input_data	logged_at
1	add_product	1062	23000	Duplicate entry 'Notebook' for key 'products.name'	name=Notebook, cat=Stationery, price=5.99, stock=100	2026-02-23 10:15:32
2	add_product	1644	45000	Price must be greater than zero	name=Free Item, cat=Promo, price=-5.00, stock=10	2026-02-23 10:15:45

Error Handling Summary

SQLEXCEPTION handler — Catches all errors (the catch-all).
SQLWARNING handler — Catches non-fatal warnings.
NOT FOUND handler — Catches "no rows" conditions (cursors, SELECT INTO).
Error code handlers (1062, etc.) — Catch one specific error type.
SIGNAL — Throw your own custom errors for business rules.
RESIGNAL — Re-throw a caught error to the caller.
GET DIAGNOSTICS — Read error details (code, state, message) after catching.
CONTINUE vs EXIT — CONTINUE keeps running after error; EXIT stops the block.