[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)