Re: SQL Stored Procedure with variable table names

I haven't done any native stored procedures.  Normally I do them in RPG. 
In RPG I would have to consider one of 2 items:

1)  lib would have a prefix of a colon to denote it as a variable.  Like 
(but different):
fetch c1 into :lib;
...
insert into :lib.table123...

2)  Does a native stored procedure support PREPARE or EXECUTE IMMEDIATE? 
Then you could construct the statement on the fly.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"chris beck" <cbeck@xxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
09/07/2004 03:50 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
<midrange-l@xxxxxxxxxxxx>
cc

Fax to

Subject
SQL Stored Procedure with variable table names






Does anyone know of a way to use a variable in a stored procedure as the 
table name? When I run the procedure, it always tries to use lib not the 
contents of the variable.

I am tying to update a specific table in every library that it exists. 
Here is a sample of the code:

DECLARE lib VARCHAR(128);
DECLARE c1 Cursor FOR
                 SELECT table_schema 
                                 FROM SYSIBM.TABLES 
                                 WHERE table_name = 'TABLE123';
 
FETCH c1 INTO lib;
WHILE at_end = 0 DO
                 INSERT INto lib.TABLE123 VALUES('A','B','C');
                 FETCH c1 INTO lib;
END WHILE;


Thanks, 

Chris Beck
-------------------------------------------------

This email transmission and any documents, files or previous

email messages attached to it may contain information that is

confidential or legally privileged. If you are not the intended

recipient, you are hereby notified that any disclosure, copying,

printing, distributing or use of this transmission is strictly

prohibited. If you have received this transmission in error,

please immediately notify the sender by telephone or return

email and delete the original transmission and its attachments

without reading or saving in any manner.



The Evangelical Lutheran Good Samaritan Society.

---------------------------------------------------------
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.