CODEWITHSUNDEEP
sqlstored-proceduresparametersdb2resultset
Thevagabond
Thevagabond

Reputation: 323

Adding two row counts in stored procedure in DB2 with parameters

I am trying to create a stored procedure that uses select to get the amount of rows in two tables and then prints out the result.

CREATE PROCEDURE TEST(IN tab1 varchar(20), IN tab2 varchar(20), out msg INTEGER)
LANGUAGE SQL
READS SQL DATA
BEGIN


DECLARE r1 CURSOR WITH RETURN FOR
    SELECT COUNT(*) FROM tab1;

DECLARE r2 CURSOR WITH RETURN FOR
    SELECT COUNT(*) FROM tab2;

OPEN r1;

OPEN r2;

---- somehow add the 2 together -----

CALL DBMS_OUTPUT.PUT( 'Zeilen = ' );
CALL DBMS_OUTPUT.PUT_LINE( msg );

END @

I updated the whole source code adding cursors since a select (variable)= something doesn't seem to work like 

 SELECT tableOneCount = (SELECT COUNT(*) FROM tab1);

But now the problem is that creating the procedure I get the error that tab1 is unknown which is obvious since it is a variable and I don't know how to add the 2 cursors.

Any ideas?

Thanks in advance.

TheVagabond

Upvotes: 0

Views: 1469

Answers (1)

AngocA
AngocA

Reputation: 7693

As @mustaccio said, you should use variables

DECLARE QTY INTEGER;
SET QTY = (SELECT COUNT(*) FROM tab1);

Upvotes: 1

Related Questions