How to for dummies

[Robert Stearns]

In an SQL procedure which I'm going to use for a model for several
others, I tried:

DECLARE v_schema character(8) default 'is2';
SELECT bhid INTO v_tmp_bhid FROM v_schema.animals
WHERE prefix=v_tmp_prefix
AND regnum=v_tmp_regnum;

so that I didn't have to change the schema in every SQL statement in the
procedure.

It failed, as I really expected, pointing to v_schema.animals as an
unknown name. The question is: how do I achieve the substitution for the
schema, the table name, or both together throughout an SQL procedure?

[Serge Rielau]

IF your schema is fixed you do this:

-- Set the default schema fro table resolution
SET SCHEMA = IS2
@
-- Set the default PATH for function resolution
SET PATH = CURRENT PATH, I2
@
-- Create procedure using CURRENT SCHEMA and CURRENT PATH
CREATE PROCEDURE
BEGIN
....
SELECT bhid INTO v_tmo_bhid FROM animals WHERE ...
END
@

If your schema is provided by the caller of the procedure
You will need to use dyamic SQL. Check out
PREPARE/EXECUTE or EXECUTE IMMEDIATE

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

IF your schema is fixed you do this:

-- Set the default schema fro table resolution
SET SCHEMA = IS2
@
-- Set the default PATH for function resolution
SET PATH = CURRENT PATH, I2
@
-- Create procedure using CURRENT SCHEMA and CURRENT PATH
CREATE PROCEDURE
BEGIN
....
SELECT bhid INTO v_tmo_bhid FROM animals WHERE ...
END
@

If your schema is provided by the caller of the procedure
You will need to use dyamic SQL. Check out
PREPARE/EXECUTE or EXECUTE IMMEDIATE

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Robert Stearns]

Serge Rielau wrote:

IF your schema is fixed you do this:

-- Set the default schema fro table resolution
SET SCHEMA = IS2
@
-- Set the default PATH for function resolution
SET PATH = CURRENT PATH, I2
@
-- Create procedure using CURRENT SCHEMA and CURRENT PATH
CREATE PROCEDURE
BEGIN
...
SELECT bhid INTO v_tmo_bhid FROM animals WHERE ...
END
@

If your schema is provided by the caller of the procedure
You will need to use dyamic SQL. Check out
PREPARE/EXECUTE or EXECUTE IMMEDIATE

Cheers
Serge

Thank you; I forgot about SET SCHEMA = IS2. It's effect on the following
is static, correct? In this case, it makes no difference, but I want to
be sure I understand.

[Robert Stearns]

Serge Rielau wrote:

IF your schema is fixed you do this:

-- Set the default schema fro table resolution
SET SCHEMA = IS2
@
-- Set the default PATH for function resolution
SET PATH = CURRENT PATH, I2
@
-- Create procedure using CURRENT SCHEMA and CURRENT PATH
CREATE PROCEDURE
BEGIN
...
SELECT bhid INTO v_tmo_bhid FROM animals WHERE ...
END
@

If your schema is provided by the caller of the procedure
You will need to use dyamic SQL. Check out
PREPARE/EXECUTE or EXECUTE IMMEDIATE

Cheers
Serge

Thank you; I forgot about SET SCHEMA = IS2. It's effect on the following
is static, correct? In this case, it makes no difference, but I want to
be sure I understand.

[Serge Rielau]

Correct, the effect is static for the static queries. It's dynamic for
the dynamic queries.
All your static queries will use teh schema as in effect at CREATE
PROCEDUER time. All dynamic queries will use the schema as set by the
caller (inherited) or within the procedural flow.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

Correct, the effect is static for the static queries. It's dynamic for
the dynamic queries.
All your static queries will use teh schema as in effect at CREATE
PROCEDUER time. All dynamic queries will use the schema as set by the
caller (inherited) or within the procedural flow.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab