Temp table #tempsum has changed since last used.

[former_SQLA_member1694892]

I have a proc that is dynamically creating the columns of a report, based upon the number of terms that fall within my reporting period. So perhaps I run it one time and get a student number followed by 8 thems. Then run it again with another range and get student numbers followed by 10 terms.

#tempsum is created and poplulated by looping through student terms and creating #tempsum by 'Select ...into #tempsum. (I found this great way to pivot a table on this forum, a long time ago!) It runs fine the first time, but when I run it again, I get "Temp table #tempsum has changed since last used." (sqlcode 1397). This would be true if I use a different timeframe. But I thought the table would completely drop at the end of the procedure, each time I run it. I even tried to add 'drop table if exists #tempsum;' at the beginning of the proc. Any ideas how to tackle this error? Here is the looping section of the proc.

  drop table if exists #tempsum;
  set @sql = 'Select distinct tstudnum as ''Student'', tname as ''Name'', tclass as ''Class'','|| 
    'tperioddesc as ''Period'', tplanshort as ''PayPlan'','|| 
    'tbalance as ''Balance'', tperbalance as ''PeriodBalance'', tlastpay as ''LastCashTrans''';
  /* now loop through the term hours and add on to the sql statement for all terms.  */
  /* If you view the results of the 'message' you see the outcome!                  */
  for f_fetch
  as c_fetch no scroll cursor for
    select distinct tterm ,tinternalterm
     from tempbundle 
    order by tinternalterm
    for read only
  do 
    set @sql = trim(@sql)||',sum(( if tterm='''||trim(tterm)||''' then 1 else 0 endif) * coalesce(thours,0)) as "'||trim(tterm)||'"'; 
  end for;
  set @sql =  trim(@sql)||' into #tempsum from tempbundle group by student,name,class,payplan,Period,balance,periodbalance,lastcashtrans;'; 
  execute immediate @sql;

  select distinct  * from #tempsum order by payplan,period,student;

[former_SQLA_member1694892]

Don't know how I got the big font in there!

[VolkerBarth]

The leading hash at "#tempsum" seems to be interpreted as a headline marker. Masking does help.:)

[VolkerBarth]

As the temp table's seems to have known fields, does the error also appear if you create the temp table explicitly and use "INSERT...SELECT" instead of "SELECT INTO..." ? - FWIW, in case you are using SA 17, there's a builtin PIVOT operator so you could get rid of dynamic SQL.

[former_SQLA_member1694892]

I may end up explicitly defining the table if I can't clear this up. The columns are known to be terms, but "which" terms, and the number of terms, are dependant upon calendar range we're looking at. So the dynamic works great for this situation. And thank-you! We are on 16 but I did see something about the pivot in 17. For future use it will be great!

[VolkerBarth]

Ah, I misread the code, of course the temp table's structure does vary so my suggestion won't work.

Does the final statement build the procedure's result set? If so, does ALTER PROC... RECOMPILE make a difference here?

Cf. the accepted answer within this similar FAQ- you might need ot use EXECUTE IMMEDIATE WITH RESULT SET ON - also on the final select.

[former_SQLA_member1694892]

A wise co-worker has found a solution. Apparently, it's the final "select" that's causing the error: select distinct * from #tempsum order by payplan,period,student;

By changing this to: EXECUTE IMMEDIATE WITH RESULT SET ON 'select distinct * from #tempsum order by payplan,period,student';

it fixes the issue. I can run the proc any number of times with different academic calendar ranges. So I'm happy, although it bugs me that I don't know why this fixes the issue! I still would think the #tempsum table would drop at the completion of the procedure.

[VolkerBarth]

See my last comment above, I guess the cited answer will help.

[former_SQLA_member1694892]

Well, very interesting. Thanks for referring to the previous post. I had looked of course but didn't find that one.
AS always, I appreciate the answers and hints here!