2

Here is a really simple example of a select procedure I'd like to create, although it's only partly done (it's pretty self-explanatory):

delimiter //
CREATE PROCEDURE mysql.selectTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime,  _hashid int,
  _id_status bit(2), _id int, _criminal_status bit(1), _dob int, _stateid int, _stateid_status bit(1))
begin
  START TRANSACTION;

  SELECT `first_name`, `last_name`, `hashID` FROM MYDB.person;

  SELECT`id`, `id_status`, `criminal_status`, `hashID` FROM MYDB.id_crimes;

  SELECT `stateID`, `stateID_status`, `dob`, `hashID` FROM MYDB.dob_state;

  SELECT `create_time`, `update_time`, `hashID` FROM MYDB.timestamps;

  COMMIT;
end//
delimiter ;

What I'm looking for is the absolutely most concise way to select a record in its entirety, keeping it as a procedure. I'd really like to avoid joins on the tables, but I don't really see any way around doing a join on the hashID, given the abovementioned restrictions. Is there another way? Pointers? What would that look like in a procedure? I'd like to see someone's concise correction of what I want to do. :) Thanks!

2
  • 1
    I'd really like to avoid joins on the tables <-- WHY? Did someone sell you snake oil that all joins always slow down all queries no matter what? – Aaron Bertrand Apr 27 '16 at 1:47
  • No...it's for an experiment (comparing performance); I already have joins implemented. Not sure that whether or not someone has sold me "snake oil" is really relevant to my question, though. :) If you want to demonstrate a super efficient way to use a join within the procedure, I'm happy to take a look anyway! – Lori Apr 27 '16 at 1:56
2

You can use variables:

delimiter //
CREATE PROCEDURE mysql.selectTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime,  _hashid int,
  _id_status bit(2), _id int, _criminal_status bit(1), _dob int, _stateid int, _stateid_status bit(1))
begin
  START TRANSACTION;

  SELECT @fn:=`first_name`, @ln:=`last_name`, @hid:=`hashID` FROM MYDB.person WHERE <condition>;

  SELECT @id:=`id`, @s:=`id_status`, @cs:=`criminal_status` FROM MYDB.id_crimes WHERE `hashID` = @hid;

  SELECT @sid:=`stateID`, @sids:=`stateID_status`, @dob:=`dob` FROM MYDB.dob_state WHERE `hashID` = @hid;

  SELECT @ct:=`create_time`, @ut:=`update_time` FROM MYDB.timestamps WHERE `hashID` = @hid;

  COMMIT;

  SELECT @fn AS `first_name`, @ln AS `last_name`, @hid AS `hashID`,
         @id AS `id`, @s AS `id_status`, @cs AS `criminal_status`,
         @sid AS `stateID`, @sids AS `stateID_status`, @dob AS `dob`,
         @ct AS `create_time`, @ut AS `update_time`;
end//
delimiter ;

Edit:

You can also use SELECT <columns,...> INTO <variables,...>:

You can use variables:

delimiter //
CREATE PROCEDURE mysql.selectTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime,  _hashid int,
  _id_status bit(2), _id int, _criminal_status bit(1), _dob int, _stateid int, _stateid_status bit(1))
begin
  START TRANSACTION;

  SELECT `first_name`, `last_name`, `hashID` INTO @fn, @ln, @hid FROM MYDB.person WHERE <condition>;

  SELECT `id`, `id_status`, `criminal_status` INTO @id, @s, @cs FROM MYDB.id_crimes WHERE `hashID` = @hid;

  SELECT `stateID`, `stateID_status`, `dob` INTO @sid, @sids, @dob FROM MYDB.dob_state WHERE `hashID` = @hid;

  SELECT `create_time`, `update_time` INTO @ct, @ut FROM MYDB.timestamps WHERE `hashID` = @hid;

  COMMIT;

  SELECT @fn AS `first_name`, @ln AS `last_name`, @hid AS `hashID`,
         @id AS `id`, @s AS `id_status`, @cs AS `criminal_status`,
         @sid AS `stateID`, @sids AS `stateID_status`, @dob AS `dob`,
         @ct AS `create_time`, @ut AS `update_time`;
end//
delimiter ;
4
  • 1
    @Lori I added an alternative using SELECT INTO which should not have that issue. – Ezequiel Tolnay Apr 28 '16 at 4:35
  • I believe you copied and pasted the exact same procedure twice above, if you want to take a look. – Lori Apr 28 '16 at 4:43
  • @Lori nope, all of the SELECT FROM are now SELECT INTO FROM. – Ezequiel Tolnay Apr 28 '16 at 4:44
  • Ah yes, I see that now. :) – Lori Apr 28 '16 at 4:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.