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;

asked 21 Apr, 09:58

rsnyder's gravatar image

rsnyder
39191025
accept rate: 0%

edited 21 Apr, 10:02

Volker%20Barth's gravatar image

Volker Barth
37.4k343510776

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

(21 Apr, 09:59) rsnyder
Replies hidden

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

(21 Apr, 10:03) Volker Barth

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.

(21 Apr, 10:15) Volker Barth

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!

(21 Apr, 10:48) rsnyder
Replies hidden
2

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.

(21 Apr, 11:26) Volker Barth

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.

permanent link

answered 21 Apr, 11:32

rsnyder's gravatar image

rsnyder
39191025
accept rate: 0%

1

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

(21 Apr, 11:43) Volker Barth
1

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!

(21 Apr, 11:57) rsnyder
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×24
×12
×8

question asked: 21 Apr, 09:58

question was seen: 83 times

last updated: 21 Apr, 11:57