SQL-complete-tutorial

1
DROP PROCEDURE IF EXISTS get_clients_by_state;
2

3
DELIMITER $$
4
CREATE PROCEDURE get_clients_by_state
5
(
6
	state CHAR(2)
7
)
8
BEGIN
9
	/* IF state IS NULL THEN
10
		SELECT * FROM clients;
11
	ELSE 
12
		SELECT * FROM clients c
13
		WHERE c.state = state;
14
		-- SET state = 'CA';
15
	END IF; */
16
    -- cleaner code:
17
    SELECT * FROM clients c
18
    WHERE c.state = IFNULL(state, c.state);
19
    
20
END $$
21
DELIMITER ;
22

23
-- Write a stored procedure to return invoices
24
-- for a given client
25
-- 
26
-- get_invoices_by_client
27

28
DROP PROCEDURE IF EXISTS get_invoices_by_client;
29

30
DELIMITER $$
31
CREATE PROCEDURE get_invoices_by_client
32
(
33
	client_id INT
34
)
35
BEGIN
36
	SELECT * FROM invoices i
37
    WHERE i.client_id = client_id;
38
END $$
39
DELIMITER ;
40

41
-- Write a stored procedure called get_payments
42
-- with two parameters
43
--
44
-- client_id => INT
45
-- payment_method_id => TINYINT
46

47
DROP PROCEDURE IF EXISTS get_payments;
48

49
DELIMITER $$
50
CREATE PROCEDURE get_payments
51
(
52
	client_id INT,
53
    payment_method_id TINYINT
54
)
55
BEGIN
56
	SELECT * FROM payments p
57
    WHERE p.client_id = IFNULL(client_id, p.client_id) AND
58
		p.payment_method = IFNULL(payment_method_id, p.payment_method);
59
END $$
60
DELIMITER ;
61