I am writing a simple MySQL stored procedure:
DELIMITER $ DROP PROCEDURE IF EXISTS GetUserByCaseId $ CREATE DEFINER = 'DEV_Organization'@'localhost' PROCEDURE GetUserByCaseId (IN searchedForId VARCHAR(8)) LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA SQL SECURITY DEFINER BEGIN SELECT CaseIdAuthenticator.sid AS sid, CaseIdAuthenticator.caseId AS caseId, User.firstName AS firstName, User.lastName AS lastName, User.position AS position, User.email AS email FROM CaseIdAuthenticator INNER JOIN User ON User.sid = CaseIdAuthenticator.sid WHERE CaseIdAuthenticator.caseId = searchedForId LIMIT 1; END $
It works:
mysql> CALL DEV_Organization.GetUserByCaseId("bro4"); +------+--------+-----------+----------+----------+----------------------+ | sid | caseId | firstName | lastName | position | email | +------+--------+-----------+----------+----------+----------------------+ | 3773 | bro4 | Billy | O'Neal | | billy.oneal@case.edu | +------+--------+-----------+----------+----------+----------------------+ 1 row in set (0.00 sec)
but unfortunately, allows the client to leave with NULL passing:
mysql> CALL DEV_Organization.GetUserByCaseId(NULL); Empty set (0.00 sec)
I would rather throw an error instead. How can i do this? (Just setting it to VARCHAR(8) NOT NULL
causes MySQL to throw an error when creating the procedure ...)
EDIT: Commentators have asked for my rationale. I am working with a database API in PHP land that looks like this:
private function ProcedureInner($suffix, $procedure, $arguments = array()) { $suffix = (string)$suffix; $procedure = (string)$procedure; $questionMarks = ''; $args = count($arguments); if ($args > 0) { $questionMarks = '?'; for ($idx = 1; $idx < $args; ++$idx) { $questionMarks .= ', ?'; } } $stmt = $this->pdo->prepare("CALL `{$this->mode}_{$suffix}`.`{$procedure}` ({$questionMarks})"); $stmt->execute($arguments); return $stmt; } public function ProcedureScalar($schema, $procedure, $arguments = array()) { $result = $this->ProcedureInner($schema, $procedure, $arguments); $answer = $result->fetchColumn(0); $result->closeCursor(); return $answer; }
notice how easy it is for the client to forget to pass an argument in an array of arguments However, this is the low-level part of database abstraction, I do not want to put the constraints that certain arguments look for here.