Has any one know how to create temporary tables inside dynamic SQL?.

[bala]

it seems like i am able to create it. But when i try to access that
temporary table, i get an error "Invalid object".
this is happening only when i try to create local temporary table.
Global temporary table works fine inside the dynamic SQL.

Any Help appreciated.

[AB]

A dynamic SQL statement works in its own execution environment, like a stored procedure. At the tremination of a stored procedure all the local temporary tables (#...) are deleted, because this is the semantics of local temporatry tables. The same applies to the execution of a dynamic SQL statement.

If you want to communicate a temporary data table between a script and a dynamic SQL statement, you have to use a "global" temporary table, and the same applies to a stored procedure and the invoking script. This is the semantic of global temporaty tables.

Regards, AB


------------
bala at 7/28/00 5:47:01 PM

it seems like i am able to create it. But when i try to access that
temporary table, i get an error "Invalid object".
this is happening only when i try to create local temporary table.
Global temporary table works fine inside the dynamic SQL.

Any Help appreciated.