stored procedure with parameters mysql

1-- Stored Procedure with parameters with default
2
3DELIMITER $$
4
5CREATE PROCEDURE get_clients_by_state
6(
7	state CHAR(2)
8)
9BEGIN
10	IF state IS NULL THEN
11    	SET state = 'CA';
12    END IF;
13
14	SELECT 
15		*
16	FROM
17		clients c
18    WHERE c.state =  state;
19END $$
20
21DELIMITER ;
22
23
24-- Once you save the stored procedure, you can invoke it by using the CALL statement:
25
26CALL get_client_by_state(NULL);

1-- Stored Procedure with parameters
2
3DELIMITER $$
4
5CREATE PROCEDURE get_clients_by_state
6(
7	state CHAR(2)
8)
9BEGIN
10	SELECT 
11		*
12	FROM
13		clients c
14    WHERE c.state =  state;
15END $$
16
17DELIMITER ;
18
19
20-- Once you save the stored procedure, you can invoke it by using the CALL statement:
21
22CALL get_client_by_state('CA');
23

1-- Stored Procedure with parameters with default
2-- this examble return all clients in all states if parameter is NULL
3
4DELIMITER $$
5
6CREATE PROCEDURE get_clients_by_state
7(
8	state CHAR(2)
9)
10BEGIN
11	SELECT 
12		*
13	FROM
14		clients c
15    WHERE c.state =  IFNULL(state, c.state);
16END $$
17
18DELIMITER ;
19
20
21-- Once you save the stored procedure, you can invoke it by using the CALL statement:
22
23CALL get_client_by_state(NULL);

1
2        
3            
4        
5     DELIMITER //
6
7CREATE PROCEDURE GetOfficeByCountry(
8	IN countryName VARCHAR(255)
9)
10BEGIN
11	SELECT * 
12 	FROM offices
13	WHERE country = countryName;
14END //
15
16DELIMITER ;Code language: SQL (Structured Query Language) (sql)