-- Importing Test Table

DROP TABLE IF EXISTS PhotoDawgLenses;

CREATE TABLE PhotoDawgLenses(
	lensID smallint unsigned NOT NULL auto_increment,
	lensName varchar(64) NOT NULL,
	minZoom mediumint unsigned NOT NULL,
	maxZoom mediumint unsigned NOT NULL,
	minAperture decimal(6,3) unsigned NOT NULL,
	maxAperture decimal(6,3) unsigned NOT NULL,
	hasED enum('Yes', 'No') NOT NULL DEFAULT 'No',
	hasVR enum('Yes', 'No') NOT NULL DEFAULT 'No',
	PRIMARY KEY (lensID),
	UNIQUE KEY (lensName)
) ENGINE='InnoDB';

DROP PROCEDURE IF EXISTS AddLensTestProc;

-- Setting sql_mode prior to importing procedure
SET SESSION sql_mode='TRADITIONAL';

DELIMITER //

CREATE PROCEDURE AddLensTestProc (
	IN inLensName varchar(64), 
	IN inMinZoom mediumint unsigned, 
	IN inMaxZoom mediumint unsigned,
	IN inMinAperture decimal(6,3) unsigned,
	IN inMaxAperture decimal(6,3) unsigned,
	IN inHasED enum('Yes', 'No'),
	IN inHasVR enum('Yes', 'No')
)
LANGUAGE SQL READS SQL DATA
main:BEGIN
	SHOW variables like 'sql_mode%';
	
	IF inMinZoom > inMaxZoom THEN
		LEAVE main;
	END IF;
	IF inMinAperture > inMaxAperture THEN
		LEAVE main;
	END IF;
	IF inHasED != 'Yes' THEN
		SET inHasED = 'No';
	END IF;
	IF inHasVR != 'Yes' THEN
		SET inhasVR = 'No';
	END IF;
	INSERT INTO PhotoDawgLenses (lensName, minZoom, maxZoom, minAperture, maxAperture, hasED, hasVR) VALUES
		(inLensName, inMinZoom, inMaxZoom, inMinAperture, inMaxAperture, inHasED, inHasVR);
END //

DELIMITER ;

-- Resetting sql mode back ot default (as if this was a new connection)
SET SESSION sql_mode='';

-- Here is the reuslt from the information_schema showing the the SQL_MODE is properly defined
SELECT ROUTINE_NAME, SQL_MODE FROM information_schema.ROUTINES WHERE ROUTINE_NAME='AddLensTestProc'\G
-- The follwoing should now work - inMinZoom is less than 0 and 'Bad' is not a value ENUM for inHasVR";
CALL AddLensTestProc('proctest9', -1, 1, 1, 1, 'Yes', 'Bad');
SELECT * FROM PhotoDawgLenses;
TRUNCATE PhotoDawgLenses;
-- However, even though the SQL Mode is set, MySQL is NOT flagging an error, although it should";
-- Setting the sql_mode manually works, but this should not be needed since the procedure already has a sql_mode defined";
SET SESSION sql_mode="TRADITIONAL";
CALL AddLensTestProc('proctest9', -1, 1, 1, 1, 'Yes', 'Bad');