Starting MySQL Routines and Scripting

Question

I have been using MySQL for a while, but only with basic queries and some joins. I want to start using Stored Procedures but I am having trouble finding a good resource to help me with what I want to know.

The main thing I want to know is how to check the value of any field in the entire database before returning anything. For example: a table that defines users, and a table that defines what permissions users have. I want to check if there is a certain permission -> user relationship before doing anything.

Here is a simplified table diagram:

Users:
ID     |   Username
1      |   User1
2      |   User2
3      |   User3
N      |   UserN

Permissions:
User ID      |     Permission ID
1            |     1
2            |     1
1            |     2
etc etc

So basically, I want to turn the following pseudo code into a MySQL Routine:

if (SELECT * FROM Permissions WHERE 'User ID' = ? and 'Permission ID' = ? returns at least one row) {
      execute privileged sql
      return true
 }
 return false

I know I may be asking a lot here, but any help would be greatly appreciated!

Answer 1

Here's an example:

drop table if exists Permissions;

create table  Permissions (
    id int,
    username varchar(50)
);
insert into Permissions (id, username) 
select 1, 'Gump'
union select 2, 'Forrest';

drop procedure if exists CheckPermissions;

DELIMITER //
 CREATE PROCEDURE CheckPermissions(
    IN checkPermission int,
    IN checkUser varchar(50),
    IN execSql varchar(512))
   BEGIN
   DECLARE declSql varchar(512);
   IF EXISTS (SELECT *  FROM Permissions p 
              WHERE p.id = checkPermission AND p.username = checkUser) 
   THEN
     SET @declSql = execSql;
     PREPARE stmt FROM @declSql;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
   END IF;
   END //
 DELIMITER ;

call CheckPermissions(1, 'Gump', 'select 1 as PermissionOk');
call CheckPermissions(2, 'Gump', 'select 2 as NotExecuted');

This prints:

PermissionOK
1

Procedures in MySQL are a pain to use and develop. For example, I had to copy the parameter SQL to a declared variable just because it doesn't work otherwise; the documentation offers no clue why.

I'd implement this logic on the client if at all possible.