Solutions for fixing stored procedures that rely heavily on global temporary tables?

Question

I am working on re-writing some stored procedures. The last issue was an abuse of EXECUTE AS USER='someUser' to use the default schema and work against those.

The second thing I just noticed though is a large use of global temporary tables. As far as I know global temporary tables are shared across all users and across all sessions. Our production and development database are on the same SQL Server Instance which seems like a problem with this design. The reason being that much of the logic in these stored procedures is dynamically run sql like SELECT * INTO ##tempTable1 FROM someTable because someTable is also dynamically generated and the original author didn't want to have to examine someTable to determine the shape, and the temp table would just take the data no matter the columns, at least that is my suspicion.

I cannot say what this application actually is used for but it is used in many (90+) locations around the US, and these stored procedures are used to get data to the user, or write data into the database. So it seems to me like this has a lot of potential to get the wires crossed - it has happened before where test data got into production and no one knew how, but now that I am working on this I suspect this must have been how - QA was running a test at the same time a real live person was using the production, wires got crossed in a race condition, the test data won out and that is what got saved instead of the real data was lost.

My first question is if my assessment is correct- I am not a SQL Server expert, never used temporary tables before but based on what I read this usage is incredibly problematic, especially for this use case of these stored procedures. Could this be the cause of test data getting into production with this setup?

My second question is what is the solution? Would it be as simple as changing every ## to a # to make it local to that procedure? Are there any pitfalls I need to be aware of with doing this? Or is there something else I should be doing to make this safer.

Answer 1

My first question... Could this be the cause of test data getting into production with this setup?

Sure, if your QA team and other tests happen in the production database on the production server. Aside from what sounds like a problematic design with global temp tables, this is a red flag in itself, if you guys do your testing in the production database.

My second question is what is the solution? Would it be as simple as changing every ## to a # to make it local to that procedure? Are there any pitfalls I need to be aware of with doing this? Or is there something else I should be doing to make this safer.

That's going to heavily depend on where and how these temp tables are used, and we can't provide a complete answer with the information provided.

If they are used within only the scope of the stored procedure that creates them, or in the chain of calling stored procedures, then a local temp table would be in scope. But if these global temp tables are used in other places that would be out of scope for a local temp table, such as with BCP to generate a drop file to a folder share, then it won't be as simple as switching to a local temp table. It'll probably involve re-designing that specific process.