Create Table As (Select...) pass a parameter

[darkman0101]

Hi there.
I'm a bit stumped.

I'm trying to create a Stored Proc that will create some temporary tables on the fly.
The thing is that the data that populates those tables has selection criteria where one of the values comes from a parameter passed into the proc.

For Example:

Create Procedure dev_jm/CATSLERPT1
(pParamin Dec(4))

LANGUAGE SQL
MODIFIES SQL DATA
SET OPTION DBGVIEW = *SOURCE

BEGIN

create table testdual as (select * from dual
where ID = pParamIn) with data;
END;

Gives the error:
SQL0205 30 10 Position 71 Column pParamIn not in table DUAL in DEV_JM.

It's like the variable is out of the scope of the create table statement.

If I remove the create table clause the proc will compile fine.

I can't find this "limitation" documented anywhere.

Can someone enlighten me?

Cheers,
Jas

 

[Pikkunero]

I don't see anything else different here from my somewhat similar stored procedure except this:

In the create procedure i have defined the parameter as IN.

In you case

Create Procedure dev_jm/CATSLERPT1(IN pParamin Dec(4))

Also between the BEGIN and END i have declared one additional variable. In your case

BEGIN
DECLARE VARIABLE AdditVar Dec(4);

SET AdditVar = pParamin;
create table testdual as (select * from dual where id = AdditVar) with data;
END

I haven't tested this so i'm not sure if it makes any difference