What is the Best Way to detect if server is a Master inside a Stored Procedure?
This will work if the Master has a Slave Connected

SELECT COUNT(1) FROM
(SELECT user FROM information_schema.processlist) A
INNER JOIN
(SELECT user FROM mysql.user B WHERE repl_slave_priv = 'Y')
USING (user);


If the answer is...


> 0, Replication is running (This indicates this is a Master)
= 0, Replication is not running


You can also check the global status variable 'Slave_running'

SELECT COUNT(1) FROM information_schema.global_status
WHERE variable_name = 'Slave_running'
AND variable_value = 'ON';


If the answer is...


> 0, Replication is running (This indicates this is a Slave)
= 0, Replication is not running


You can also check the processlist for 'system user'

SELECT COUNT(1) FROM information_schema.processlist
WHERE user = 'system user';


If the answer is...


= 2, Replication is running (This indicates this is a Slave)
= 1, Replication is broken (This indicates this is a Slave)
= 0, Replication is not running


I hope these queries give you some direction.

CAVEAT

SHOW MASTER STATUS; just tells you what the current binary log is. If your Slaves  have binary logs disabled, then SHOW MASTER STATUS; return nothing. There is no way to capture this in the information_schema database.

The only obvious way is to either


SHOW SLAVE STATUS\G
Look for the master.info file in the OS


UPDATE 2011-12-30 12:50 EDT

Here is simply way to identify your master from your slaves: Create a Table whose sole purpose to is to hold the server names that make up your Master. Run these commands on all your DB Servers, (Masters and Slaves)

CREATE TABLE mysql.MasterList
(
    hostname VARCHAR(64),
    PRIMARY KEY (hostname)
) ENGINE=MyISAM;
INSERT INTO mysql.MasterList VALUES ('dbserver1'),('dbserver2'),('dbserver3');


Now just run this query to determine if it is a Master:

SELECT COUNT(1) INTO @IsThisMaster FROM 
(SELECT variable_value hostname FROM information_schema.global_variables
WHERE variable_name='hostname') A
INNER JOIN mysql.MasterList B USING (hostname);


Here is a sample run on my PC using MySQL 5.5.12

mysql&gt; show variables like 'hostname';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| hostname      | LW-REDWARDS2 |
+---------------+--------------+
1 row in set (0.00 sec)

mysql&gt; CREATE TABLE mysql.MasterList
    -&gt; (
    -&gt;     hostname VARCHAR(64),
    -&gt;     PRIMARY KEY (hostname)
    -&gt; ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql&gt; INSERT INTO mysql.MasterList VALUES ('dbserver1'),('dbserver2'),('dbserver3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql&gt; SELECT COUNT(1) INTO @IsThisMaster FROM
    -&gt; (SELECT variable_value hostname FROM information_schema.global_variables
    -&gt; WHERE variable_name='hostname') A
    -&gt; INNER JOIN mysql.MasterList B USING (hostname);
Query OK, 1 row affected (0.01 sec)

mysql&gt; SELECT @IsThisMaster;
+---------------+
| @IsThisMaster |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql&gt; INSERT INTO mysql.MasterList VALUES ('LW-REDWARDS2');
Query OK, 1 row affected (0.00 sec)

mysql&gt; SELECT COUNT(1) INTO @IsThisMaster FROM
    -&gt; (SELECT variable_value hostname FROM information_schema.global_variables
    -&gt; WHERE variable_name='hostname') A
    -&gt; INNER JOIN mysql.MasterList B USING (hostname);
Query OK, 1 row affected (0.00 sec)

mysql&gt; SELECT @IsThisMaster;
+---------------+
| @IsThisMaster |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql&gt;


Give it a Try !!!

UPDATE 2011-12-30 13:06 EDT

Here is the Stored Function you will need:

DELIMITER $$

DROP FUNCTION IF EXISTS `mysql`.`Is_This_A_Master` $$
CREATE FUNCTION `mysql`.`Is_This_A_Master` () RETURNS INT
DETERMINISTIC
BEGIN

    SELECT COUNT(1) INTO @IsThisReplicationMaster FROM
    (SELECT variable_value hostname FROM information_schema.global_variables
    WHERE variable_name='hostname') A
    INNER JOIN mysql.MasterList B USING (hostname);

    RETURN @IsThisReplicationMaster;

END $$

DELIMITER ;


Here is a Sample Call:

mysql&gt; select mysql.Is_This_A_Master();
+--------------------------+
| mysql.Is_This_A_Master() |
+--------------------------+
|                        1 |
+--------------------------+


UPDATE 2011-12-30 13:12 EDT

Just remember


Once a Server Becomes a Slave, delete that hostname from mysql.MasterList
Once a Server Becomes a Master, insert that hostname into mysql.MasterList