MySQL – Stored procedures with OUT parameters

[adsense id=”0514458240″ width=”468″ height=”60″]

This post provides sample code of a stored procedure that includes an OUT parameter. The basic concept is something to validate a username and password combination. A better way of doing this would be to write a stored function which returns a TINYINT or BIT to indicate success or failure.

DROP TABLE IF EXISTS logins;

CREATE TABLE logins
  (
     id       INT AUTO_INCREMENT,
     username VARCHAR(100),
     PASSWORD VARCHAR(100),
     PRIMARY KEY (id)
  );

INSERT INTO logins
            (username,
             PASSWORD)
VALUES      ('test',
             'test123');  

DROP PROCEDURE IF EXISTS pValidate;

DELIMITER $$
CREATE PROCEDURE pValidate(IN pUsername VARCHAR(100), IN pPassword VARCHAR(100), OUT pResult VARCHAR(100))
BEGIN
	DECLARE count INT;
	SELECT count(1) INTO count FROM logins WHERE username = pUsername AND password = pPassword;
	IF count > 0 THEN
		SET pResult = 'success';
	ELSE
		SET pResult = 'failed';
	END IF;
END;
$$
DELIMITER ;

SET @s = "";
CALL pValidate('test','test321', @s);
SELECT @s;

CALL pValidate('test','test123', @s);
SELECT @s;

The result of this code is:

mysql> source proc.sql
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+--------+
| @s     |
+--------+
| failed | 
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+---------+
| @s      |
+---------+
| success | 
+---------+
1 row in set (0.00 sec)