Nested parameterized procedure

[Barbara]

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?

[GJColeman · Accepted 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;

[Barbara]

Thanks a lot! It worked perfectly!