0

I have multiple (more than 12) SQL Stored Procedures that setup the same set of temp tables at the start of each procedure. When one of these tables needs modified I need to go make a change in each of those Stored Procedures to match. The temp tables are expected to exist by other stored procedures later in the process. I have no ability to change the process or the flow of these stored procedures or the fact of using these as temp tables, I am coming at it from a DRY approach and looking for a way to consolidate the creation of these shared temp tables into a reusable piece of code so that when changes are needed I can do it in 1 file and reduce the amount of duplication happening today.

Example of the stored procedure

CREATE OR ALTER PROCEDURE [dbo].[procedureName]
    @inputs ...
AS
BEGIN

    DROP TABLE IF EXISTS #Table1
    CREATE TABLE #Table1 (...)
    
    DROP TABLE IF EXISTS #Table2
    CREATE TABLE #Table2 (...)

    ...
    DROP TABLE IF EXISTS #Table10
    CREATE TABLE #Table10 (...)

    ...

    INSERT INTO #Table5
    SELECT * FROM data

    ...
END
TDDdev
  • 1,490
  • 1
  • 17
  • 18
  • 1
    Can you not create a stored procedure to create the temporary tables, and then call that SP from each one? Then only the SP which creates them needs modifying each time. – Steve Lovell Jul 15 '21 at 10:14
  • Or if they need to be created at different stages, create such a stored procedure for each stage. Or just one and have it paramaterised to say which ones to create. – Steve Lovell Jul 15 '21 at 10:16
  • This does not appear to work yet. If I create a separate procedure and call it I can tell that it is being called, but when I return to the calling procedure the temp tables are not associated or available. I think what is happening here is because the temp table is created in that other procedure its only accessible in it and other items it calls, but not the parent calling procedure. – TDDdev Jul 15 '21 at 12:27
  • It appears based on this article that what I would like to do is probably not possible for my specific needs. https://www.sommarskog.se/share_data.html If the tables I am using are not statically defined here or in a parent they will recompile each run, which would cause too much overhead in this case. Probably the best approach for my needs would be to create a wrapper sproc that could define the tables and do common data crunching and then call to the original sprocs – TDDdev Jul 15 '21 at 13:03
  • 1
    I was wondering whether that might be the case, and my Googling wasn't finding relevant results. I guess (if permissions allow) you could store the create "DROP + CREATE scripts" somewhere (or return them from an SP) and then execute them from the parent, but I always like to avoid dynamic SQL where possible. – Steve Lovell Jul 15 '21 at 13:35
  • 1
    You've said you have to use temporary tables, but I'm wondering if table variables could equally be used. Then you could define create a user defined table type and just need a declaration of the variable. If you absolutely had to, you could then insert from the table variable to a temp table. See https://stackoverflow.com/questions/21127745/how-do-i-create-a-temporary-table-from-a-type – Steve Lovell Jul 15 '21 at 13:56
  • Yeah I do need to keep them as temp tables for now, as I counted over 40 other procedures using these same temp tables. Nothing I can do to change those right now, and I would love to have it structured differently. I do think this suggestion for a table variable could help improve this, I will try that out. Thank you. – TDDdev Jul 16 '21 at 11:52
  • What specific DBMS is this for? Some version of SQL Server? Temporary tables should last as long as the connection, though local temporary tables are only visible to the user that created them. When trying a single stored procedure to create the tables, was it running as a different user than the other stored procedures? – outis Feb 01 '22 at 00:04

0 Answers0