0
CREATE PROCEDURE `checkAdminAccess`
(
IN accountID INT
)
BEGIN
DECLARE staffID INT;
#DECLARE num INT;    

SELECT StaffID INTO staffID
  FROM staff 
 WHERE AccountID = accountID
   AND IsAdmin = 1;

SELECT staffID;

IF (staffID IS NULL) THEN
    CALL raise(1356, 'Admin access required.');    
END IF;

END;

For any input I get the staffID as NULL. For example:

call checkAdminAccess(3); #returns null
call checkAdminAccess(6); #returns null

And my data is below:

INSERT INTO `staff` (`StaffID`,`AccountID`,`RoleID`,`ManagerID`,`IsAdmin`) VALUES (1,3,1,1,0);
INSERT INTO `staff` (`StaffID`,`AccountID`,`RoleID`,`ManagerID`,`IsAdmin`) VALUES (2,6,2,1,1);

Can someone tell me how to do select a value into a variable in Mysql?

  • Possible duplicate of [MySQL Select into variable](http://stackoverflow.com/questions/13351479/mysql-select-into-variable) – Barett Feb 29 '16 at 00:35

1 Answers1

0

The problem is that you check the return value of a procedure, which must be null, since a procedure does not return any value. It maximum returns a resultset.

Possible solutions:

  1. Change the procedure to function, that returns the staffid.
  2. Add an out parameter to the procedure and use that to retrieve the staffid. The above link also describes how to do this.
Shadow
  • 33,525
  • 10
  • 51
  • 64
  • The original intention is to throw error than selecting StaffID. `SELECT staffID;` is for debugging purposes only. I want to throw error when staffID is null (not admin). So in this case raise method is called for both scenarios, which is not desired. I have Microsoft SQL Server background where this will work fine. I would like know if there is a equivalent way of select into in Mysql. I am considering function option too. Thanks. – Pravalika Vangari Feb 29 '16 at 05:06
  • The select into part works in your code. I do not understand your question. – Shadow Feb 29 '16 at 06:44