MySQL Stored Procedures

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

DROP PROCEDURE IF EXISTS test_proc;

DELIMITER $$
CREATE PROCEDURE test_proc (
        IN p_test_id INT,
        IN p_test_string VARCHAR(255)
    )
BEGIN
  main: BEGIN

    # declare handler when duplicate key db error is thrown
    DECLARE EXIT HANDLER FOR 1062 BEGIN
        SELECT 1 AS status_code, 'Duplicate entry for input key values' AS status_message;
    END;

    # check if input parameter is NULL
    IF p_test_id IS NULL OR p_test_id < 1 THEN
        SELECT 2 AS status_code, 'Missing or invalid p_test_id parameter' AS status_message;
        LEAVE main;
    END IF;

    INSERT INTO test_table
    SET test_id = p_test_id,
        test_string = p_test_string;    

    SELECT 0 AS status_code, 'OK' AS status_message;
  END main;
END; 
$$
DELIMITER ;

# CALL test_proc(10, 'test string parameter');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

DROP PROCEDURE IF EXISTS test_proc_2;

DELIMITER $$
CREATE PROCEDURE test_proc_2 (
        IN p_search_criteria VARCHAR(100),
        IN p_page INT,
        IN p_page_size INT
    )
BEGIN
  main: BEGIN

    IF p_page IS NULL OR p_page < 1 THEN
        SET p_page = 0;
    ELSE
        SET p_page = p_page - 1;
    END IF;

    IF p_page_size IS NULL OR p_page_size < 1 THEN
        SET p_page_size = 20;
    END IF;

    PREPARE STMT FROM 'SELECT * FROM test_table 
                       WHERE ? IS NULL OR test_column_1 LIKE ? OR test_column_2 LIKE ? 
                       LIMIT ?, ?';
    SET @c1 = p_search_criteria;
    SET @c2 = CONCAT('%', p_search_criteria, '%');
    SET @start = p_page * p_page_size;
    SET @size = p_page_size;

    EXECUTE STMT USING @c1, @c2, @c2, @start, @size;
    DEALLOCATE PREPARE STMT;
  END main;
END;
$$
DELIMITER ;

# CALL test_proc_2('searched text', 1, 10);