如何获取找到行的表的名称

I am using a mysql stored procedure that checks if an ID is present in 5-6 tables. If any of those tables contains that ID, i set a flag to true. Finally I select the flag using SELECT.

我正在使用一个mysql存储过程来检查5-6表中是否存在ID。如果这些表中的任何一个包含该ID,我将标志设置为true。最后,我使用SELECT选择标志。

The parameters to the SP are as follows:

SP的参数如下:

->settings_type = "branch"
->settings_id (the id that is searched)

If the searched ID is present in any of those 6 tables, how can i know from which table the ID was found?

如果搜索到的ID存在于这6个表中的任何一个中,我如何知道从哪个表中找到了ID?

BEGIN
DECLARE boolStatus BOOL DEFAULT FALSE;

IF settings_type = "branch"
THEN
    IF ((SELECT COUNT(tblbatches.intBranchId)   FROM tblbatches         WHERE tblbatches.intBranchId        = settings_id > 0) OR 
         (SELECT COUNT(tblexams.intBranchId)        FROM tblexams           WHERE tblexams.intBranchId      = settings_id > 0) OR
         (SELECT COUNT(tblquestions.intBranchId)    FROM tblquestions   WHERE tblquestions.intBranchId  = settings_id > 0) OR
         (SELECT COUNT(tblresults.intBranchId)  FROM tblresults     WHERE tblresults.intBranchId        = settings_id > 0) OR
         (SELECT COUNT(tblstudents.intBranchId) FROM tblstudents        WHERE tblstudents.intBranchId   = settings_id > 0) OR
         (SELECT COUNT(tblsubjects.intBranchId) FROM tblsubjects        WHERE tblsubjects.intBranchId   = settings_id > 0)
        )
        THEN
            SET boolStatus := TRUE;
    END IF;
    SELECT boolStatus;
END IF;
END

1 个解决方案

#1

One way to rewrite your procedure

重写程序的一种方法

DELIMITER $$
CREATE PROCEDURE sp_name(IN settings_type VARCHAR(32), IN settings_id INT)
BEGIN
  IF settings_type = 'branch' THEN
    SELECT COALESCE(SUM(total), 0) > 0 status, 
           GROUP_CONCAT(source) source
      FROM
    (
      SELECT 'tblbatches' source, COUNT(*) total
        FROM tblbatches
       WHERE intBranchId = settings_id
       UNION ALL
      SELECT 'tblexams', COUNT(*)
        FROM tblexams 
       WHERE intBranchId = settings_id
       UNION ALL
      SELECT 'tblquestions', COUNT(*) 
        FROM tblquestions 
       WHERE intBranchId = settings_id
       UNION ALL
      SELECT 'tblresults', COUNT(*)
        FROM tblresults
       WHERE intBranchId = settings_id
       UNION ALL
      SELECT 'tblstudents', COUNT(*)
        FROM tblstudents
       WHERE intBranchId = settings_id
       UNION ALL
      SELECT 'tblsubjects', COUNT(*)
        FROM tblsubjects
       WHERE intBranchId = settings_id
    ) q 
     WHERE total > 0;
  END IF;
END$$
DELIMITER ;

Sample output when matching records have been found:

找到匹配记录时的示例输出:

| STATUS |                SOURCE |
----------------------------------
|      1 | tblbatches,tblresults |

and when they haven't

当他们没有

| STATUS | SOURCE |
-------------------
|      0 | (null) |

Here is SQLFiddle demo

这是SQLFiddle演示

#1