Variable Object Name in Dynamic Cursor

Database
Enthusiast

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

1 REPLY
Enthusiast

Re: Variable Object Name in Dynamic Cursor

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 ';