I'm having trouble with a stored proc. The problem is that the first
table is in a different database. Here is the test stored proc below.
Where I run this I get " ERROR 1327 (42000): Undeclared variable: s". I
have tried it by declaring "clientdev.servers s" and just by using
"clientdev.servers" and specifying a qualifier on each statement of
"servers." and also "clientdev.server." This is being created in the
dwhdev databased (clientdev is replicated from a different server).
DELIMITER *@@@*
DROP PROCEDURE IF EXISTS sp_selectdwhserver *@@@*
CREATE [EMAIL PROTECTED] PROCEDURE sp_selectdwhserver(
OUT P_server_id INT,
OUT P_server_load INT,
OUT P_server_fqdn VARCHAR(200),
OUT P_server_url VARCHAR(200)
)
BEGIN
DECLARE L_server_load INT DEFAULT 0;
DECLARE L_server_id INT DEFAULT 0;
DECLARE L_server_fqdn VARCHAR(200);
DECLARE L_server_url VARCHAR(200);
SELECT
(s.server_disk_space * s.server_load * s.server_ram) INTO
l_server_load,
s.server_fqdn INTO L_server_fqdn,
s.server_url INTO L_server_url,
s.server_id INTO L_server_id
FROM clientdev.servers s
WHERE s.active = 1
AND s.available = 1
ORDER BY
(s.server_disk_space * s.server_load * s.server_ram)
LIMIT 1
SET P_server_id := L_server_id;
SET P_server_load := L_server_load;
SET P_server_fqdn := L_server_fqdn;
SET P_server_url := L_server_url;
END
*@@@*
DELIMITER ;
Just as a side note, there is more going on before the end of the
procedure (thus the locally declared var's) but I haven't managed to get
this to work just yet. Also, in production, the DEFINER will be an
account that have SELECT permissions on the proper table in the proper
database (as well as EXECUTE in the dwhdev database)
Any ideas?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
