Yep, thanks Sergey. Tried it and works great. Eliminated the global temp tables. Showed it to a couple of other developers here and they thought it was really useful too.

If I get a chance, I'm revisiting the possibility of extracting the necessary data using set queries but I'm dubious with the current DB structures.

>Hi Bill,
>
>In this case you don't need Global temp table because you can return values from sp_executesql as shown in my FAQ.
>
>>Hi Sergey,
>>
>>Thanks, I'll look at that.
>>
>>On your question ... long story. :-)
>>
>>A couple of us got tapped to create a bunch of reports from data that's pretty convoluted and we have been wrestling with how to produce the data results itself. It involves counts and sums in which we could not find ways to generate using set queries. So we resorted to creating temp tables and cursors we could iterate through to generate the results. I need to get some agregate data into a local var that is then used in calculations. I would normally do something like:

>>SELECT @MyVar = SUM(MyCol) from mytable where ...

>>Problem is the name of MyCol is not known at design time. Columns have to be created on the fly so the count and names are not known until run time. As a result I have to resort to the SQL Server equivalent of VFP macros ( sp_executesql ). Problem with this is it runs the code in another process ( or sub-process ) so the local var @MyVar goes out of scope when sp_executesql returns. So I put the data into a global temporary table to get around this since it will be visible after return from sp_executesql. In order for this to work the name of the GTT has to be known at design time ( I.e. hard-coded ) which opens up the possibility of name conflicts in the DB. It has been suggested to me to wrap this code in a transaction to see if isolation can be maintained. I will look at this next.
>>
>>This has been an unmpleasant task ... and I have a long way to go. :-)
>>
>>>
>>>See if How to return a value from dynamicaly executed T-SQL code FAQ #8130 can help you. BTW, why do you've to use global temp table in the first place?
>>>