0

I have several stored procedures that first delete, then populate a global temp table called ##DataOutput. That is not all that the stored procedures do - just at some point of their process, they must populate this global table with some data, that other processes will be using.

The structure of ##DataOutput can change a little, it's not always the same.

Even though the table is being dropped at the beginning, sometimes SQL Server shows an error when calling a stored procedure, because it expects column C (for example) that is going to be generated in the current stored procedure, but was not in the last stored procedure (that is, currently the global temp table ##DataOutput does not have column C).

Is there a way to prevent SQL Server for checking these tables prior to executing, since this table is deleted at the beginning of the stored procedure?

I know that using EXEC would make it dynamic, but I'd like to check any other options.

Thank you.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
igorjrr
  • 790
  • 1
  • 11
  • 22
  • 1
    You should not be using the same table name in these procedures even if the table is temporary. Since the procedures have and obviously are running concurrently there is the potential for cross-contamination of the data. – webmite May 11 '18 at 21:28
  • @webmite Yes I'm aware of that and agree, but there are controls in place that execute one procedure at a time. Then, after executing, another procedure can be called in another session to do some work with the data inside the global temp table. – igorjrr May 11 '18 at 21:30
  • What DBMS are you using? SQL Server presumably? Please tag you question with the DBMS you're using. – sticky bit May 11 '18 at 21:42
  • 1
    @igorjrr. If there are controls in place your original statements seem to indicate they are not sufficient. I would suggest that your review why there appears to be contention for this resource. – webmite May 11 '18 at 22:00
  • The other approach to take would be to have a mechanism by which the current SP destroys the table at the end of it's processing. Then the next SP would not be approaching the resource as a 'clean slate'. – webmite May 11 '18 at 22:06

1 Answers1

0

Don't have a direct answer to the question, but I'm thinking you might be able to work around the issue by declaring the table with all of the possible fields from the get go. That way you'll never be missing a field that hasn't been augmented to the temp table yet.

James Huang
  • 101
  • 1
  • 7