I need to write a procedure that receives a database name as a parameter. The procedure must:

1) look up some information (several fields) from that database (+ a known table name) and store it in variables (or something)

2) do further processing with those values in the main procedure.

The problem is that in order to do #1, I must use dynamic SQL - and I see no way to "get the information back out" of that dynamic SQL execution for use in the rest of the main procedure. Any variables I try to assign are "local" to the dynamic SQL execute as are any temp tables I create. I could create a global temp table, but that is not my preferred choice.

How do you do this?

TIA!