Hi,
I'm trying to write a procedure where the databasename is dynamic and is selected based on a where clause, which is also dynamic.
So far I have got this:
CREATE PROCEDURE Test (IN DBName VARCHAR(100), OUT RowCount DEC(10,2)) BEGIN DECLARE SqlStr VARCHAR(1000); DECLARE C1 CURSOR FOR S1; SET SqlStr = 'SELECT count(*) FROM '|| DBNAME || '.MyTable '; PREPARE S1 FROM SqlStr; OPEN C1 USING DBName; FETCH C1 INTO RowCount; CLOSE C1; END;
I would need to add something like this now in order to make the database selection automatic, but I'm not sure of how to handle this:
WHERE DBName = (SELECT 'firstpart||EnvName||' FROM EnvTable WHERE EnvName = (SELECT EnvName FROM EnvTable WHERE Flag = 1 AND Priority = (SELECT MIN(Priority) FROM EnvTable))
Should this part of code go in the same procedure? Can I add this when I call the procedure? Any other ideas?
Solved! Go to Solution.
I think you are looking for something like this?
CREATE PROCEDURE Test (OUT RowCount DEC(10,2))
BEGIN
DECLARE SqlStr VARCHAR(1000);
declare DBNAME varchar(256) DEFAULT '';
SELECT 'firstpart||EnvName' FROM EnvTable
WHERE Flag = 1
AND Priority = ( SELECT MIN(Priority) FROM EnvTable )
into :DBNAME;
DECLARE C1 CURSOR FOR S1;
SET SqlStr = 'SELECT count(*) FROM '|| DBNAME || '.MyTable ';
PREPARE S1 FROM SqlStr;
OPEN C1 USING DBName;
FETCH C1 INTO RowCount;
CLOSE C1;
END;
I think you are looking for something like this?
CREATE PROCEDURE Test (OUT RowCount DEC(10,2))
BEGIN
DECLARE SqlStr VARCHAR(1000);
declare DBNAME varchar(256) DEFAULT '';
SELECT 'firstpart||EnvName' FROM EnvTable
WHERE Flag = 1
AND Priority = ( SELECT MIN(Priority) FROM EnvTable )
into :DBNAME;
DECLARE C1 CURSOR FOR S1;
SET SqlStr = 'SELECT count(*) FROM '|| DBNAME || '.MyTable ';
PREPARE S1 FROM SqlStr;
OPEN C1 USING DBName;
FETCH C1 INTO RowCount;
CLOSE C1;
END;
Thanks a lot! It worked perfectly!