CODEWITHSUNDEEP
sqlstored-proceduresparameterscursordb2
Thevagabond
Thevagabond

Reputation: 323

Using a parameter as a table name in a DB2 SQL cursor and adding two cursors

I am trying to parameterise a stored procedure and use the parameter as the table name. The second part is then to add the to returning numbers and assign them to another variable:

CREATE or replace PROCEDURE TEST
(IN tab1 CHAR(20), IN tab2 CHAR(20), OUT MSG INTEGER)

RESULT SETS 1
LANGUAGE SQL

BEGIN

DECLARE C1 CURSOR WITH RETURN FOR
SELECT count(*)
FROM tab1;

DECLARE C2 CURSOR WITH RETURN FOR
SELECT count(*)
FROM tab2;

OPEN C1;
OPEN C2;

SET MSG= C1 + C2;
CALL DBMS_OUTPUT.PUT_LINE( msg );

END @

The problem is, that the parameters are not set as the table name resulting in the error that tab1 is not a known table. I don't know if the adding works, didn't get that far yet, maybe you can see a mistake in that already.

I hope you can help me since this is driving me crazy.

Thanks in advance.

TheVagabond

Upvotes: 0

Views: 1182

Answers (1)

Charles
Charles

Reputation: 23783

The table name is one place you can't use parameters.

You'll need to use dynamic SQL along with a PREPARE/EXECUTE or EXECUTE IMMEDIATE. Note that this opens the door to SQL injection attacks.

Additionally, you're confusing cursors and host variables. You can't add cursors together, you can fetch from them or return them as results sets.

Take a look at SELECT INTO instead.

Upvotes: 2

Related Questions