0

I have stored procedures I need to rewrite that used global temporary tables but now must use local temporary tables so that there is no cross over between users/the prod and dev db which are on the same SQL Server instances or any other new databases this database will be share the instance with.

I have to convert a lot of code that is like this

EXEC sp_executesql 
     N'SELECT manyDynamicallyCreatedColumns INTO ##someTempTable

I now for this to work for local tables I must instead create the table first and then INSERT INTO like

CREATE TABLE #tempTable (manyColumns);

EXEC sp_executesql 
     N'INSERT INTO #tempTable SELECT manyColumns FROM somewhere'

The issue I face is that this stored procedure has 8 different scenarios that lead to the global temp table. The select statement of the SQL is dynamically generated via a number of other queries. I think what would be the easiest way for me to figure out what my CREATE TABLE #tempTable should look like is if I could print/select what global temporary table looks like after it is made, for each of these 8 scenarios. Then I would just need a If/Else If statement that creates the local table appropriately before proceeding. But I don't know how or if this is possible.

For real tables I can right click -> Script to...-> Create. I don't know if there is an analogous way to do this via scripting that works for global temporary tables. Is there?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Brian Karabinchak
  • 207
  • 1
  • 9
  • 1
    I would politely suggest that the problem to be solved here isn't global vs local temp tables, it's the reliance on either of those to solve problems at all. You have a swiss-army-knife stored procedure that's doing 8 (apparently) very different jobs. Maybe it's time to properly design a solution rather than to to perpetuate mistakes? – Damien_The_Unbeliever Jan 23 '23 at 19:24
  • It's the same job, its for 8 different products but its the same semantic business logic. It's just some products have more columns than others. Sure I could split them into 8 - but I would still need to know what this table is and looks like so I can make an analogous temporary local table or table type, or even if I have to convert to my application script - I need to know datatypes and column types so I can make an appropriate dataset structure. – Brian Karabinchak Jan 23 '23 at 19:55
  • I was able to accomplish this using the script from this post - https://stackoverflow.com/a/16797460/15414963 – Brian Karabinchak Jan 23 '23 at 20:02

0 Answers0