CODEWITHSUNDEEP
sqlselectstored-proceduresdb2
Thevagabond
Thevagabond

Reputation: 323

Create Table, insert values and then select those within stored procedure

I have this stored procedure:

CREATE or replace PROCEDURE TESTx()

BEGIN



DECLARE tableOneCount INTEGER; 
DECLARE tableTwoCount INTEGER;
DECLARE strCmd VARCHAR(500);



SET tableOneCount = (SELECT COUNT(*) FROM proj);
SET tableTwoCount = (SELECT COUNT(*) FROM proj2);

SET msg = tableOneCount + tableTwoCount;

Create table tempa(name varchar(50), counter integer);
Insert into tempa(name, counter) values ('counter1', tableOneCount);
Insert into tempa(name, counter) values ('counter2', tableTwoCount);
Insert into tempa(name, counter) values ('counter_all', msg);

SET strCmd=(SELECT * FROM tempa);

EXECUTE IMMEDIATE(strCmd);

drop table tempa;

END @

It should just count the 2 tables add them and then return a table with the results. Since I haven't figured out another way to do it I create a temp table and insert everything there to then just return a select statement. Somehow though this is not working since the select statement is running into an error.

I tried using 

declare global temporary table session.tempa
(name varchar(50), counter integer) 
on commit preserve rows not logged;

but that gives me the error, that the amount of rows and variables are not matching (SQL0117N)

Any idea on how to get this to work?

Upvotes: 0

Views: 902

Answers (1)

Rahul
Rahul

Reputation: 77926

You last statement (dynamic query formation is not correct) throwing error which should be like below

SET strCmd="SELECT * FROM tempa";
EXECUTE IMMEDIATE(strCmd);

Though you don't need to form a dynamic query statement here at all. You can just return the select like SELECT * FROM tempa.

Again your procedure can be shorten like

CREATE or replace PROCEDURE TESTx()
BEGIN

DECLARE tableOneCount INTEGER;
SET tableOneCount = (SELECT COUNT(*) FROM proj) + (SELECT COUNT(*) FROM proj2);

select 'counter1', count(*) from proj

union all

select 'counter2', COUNT(*) FROM proj2

union all 

select 'counter_all', tableOneCount from dual

END @

Upvotes: 1

Related Questions