Hi all:
I am totally new to using Procedures. What I need to do is have the Table name in the query be dynamic. The following isn't working though.

Any assistance and guidance is appreciated.

-----------------------------------------------------------------
BEGIN
DECLARE Result CURSOR WITH RETURN FOR Stmt;
DECLARE tableName VARCHAR(100);
DECLARE exportToTableName VARCHAR(100);

SET tableName = 'Clients';
SET exportToTableName = 'ExportTable_' + 'Clients';

 PREPARE Stmt FROM 'DROP TABLE ?';
 Execute Stmt USING exportToTableName;

 PREPARE Stmt FROM 'CREATE TABLE ?
                    AS SELECT Name, Description, Type, "Length", Precision, Scale, Nullable
                    FROM Information.TableColumns
                    WHERE TableName = ?
                    WITH DATA';
 OPEN Result USING exportToTableName, tableName;
END

Mc Q

The fundamental problem is you just can't do that. I forget the terminology but its not possible how you're trying to do it.

What you can do is something along the lines of



BEGIN
DECLARE Result CURSOR WITH RETURN FOR Stmt;
DECLARE tableName VARCHAR(100);
DECLARE exportToTableName VARCHAR(100);
DECLARE SQLLine VARCHAR;

SET tableName = 'Clients';
SET exportToTableName = 'ExportTable_' + 'Clients';

EXECUTE IMMEDIATE  'DROP TABLE ' + exportToTableName;

SET SQLLine = 'PREPARE Stmt FROM 'CREATE TABLE '+ exportToTableName +
' AS SELECT Name, Description, Type, "Length", Precision, Scale, Nullable '
+ ' FROM Information.TableColumns'
+' WHERE TableName = ' + tableName+ ' WITH DATA';

EXECUTE IMMEDIATE SQLLine;

END

Totally untested of course.

Roy

<<  DECLARE SQLLine VARCHAR;

SET SQLLine = ...
>>

Roy,
Thank you. Setting it to a variable first worked.

Tim:
My apologies for posting this in the wrong area. Please move to the General area.

Thank you.