All Forums Database
thompsonhab 13 posts Joined 09/12
10 Jun 2013
Variable Object Name in Dynamic Cursor

Hello,
I am trying to parameterize on object referenced in my dynamic cursor. Let's say I have a table in three different databases called AuditLogs that I want to dynamically query the database to be able to check the row count based on the database name supplied in the stored procedure.
Something like this:
REPLACE 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 '|| '?' || '.AuditLogs ';
PREPARE S1 FROM SqlStr;
OPEN C1 USING DBName;
FETCH C1 INTO RowCount;
CLOSE C1;
END;
I get an error: CALL Failed. 3707:  Test:Syntax error, expected something like a name or a Unicode delimited identifier or '(' between the 'FROM' keyword and '?'. 
How do I dynamically change the database name in the SQLStr variable based on a Stored Procedure input parameter?
 Thanks,
Todd

thompsonhab 13 posts Joined 09/12
10 Jun 2013

I think I figured it out the second I hit the submit button.
Change from:
SET SqlStr = 'SELECT count(*) FROM '|| '?' || '.AuditLogs ';
To
SET SqlStr = 'SELECT count(*) FROM '|| DBName || '.AuditLogs ';

You must sign in to leave a comment.