Re: how to create variable in sql procedure?

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 03/14/2018
02:02:08 PM:

On Wed, Mar 14, 2018 at 1:35 PM, Alan Campin <alan0307d@xxxxxxxxx>

wrote:

Create Variable is very slow. Hope you are not calling this much.

Why use an SQL procedure instead of using an SQL RPG service program?

I am calling a SQL procedure with input parms.

in the SQL procedure I want to create a new table from a select

statement,

where the "where" clause of that select uses the input parms:

CREATE or replace PROCEDURE demoProc(
in inCompanyCode char(2)
)

Create table steve1 as (
select * from custmast
where companyCode = inCompanyCode )
with data ;

But I cannot do this. Cannot have substitution variables in a create

table

as statement.

If you build your CREATE TABLE statement in a variable-character
string (dynamic SQL) then you will be able to concatenate (or replace)
your substitution variable(s) into the string with no problems. Then just
use EXECUTE IMMEDIATE. I have done this in a stored procedure with no
problem.


Sincerely,

Dave Clark