SQL Server Performance

Error in Estimated execution plan for stored procedure with temporary tables

Discussion in 'T-SQL Performance Tuning for Developers' started by geebee2, Mar 20, 2009.

  1. geebee2 New Member

    I have a stored procedure that uses explicit temporary tables ( e.g. #treatments )
    In Query Analyser (SQL Server 2000), when I click to display the Estimated Execution plan, I get an error message
    Invalid object name #treatments
    Is there any way around this?
    Thanks.
  2. Adriaan New Member

    True, the Estimated Execution plan fails if there's a temp table involved.
    During testing, you could create "permanent" tables and use those instead of temp tables.
  3. geebee2 New Member

    Thanks Adriaan
    Yes, looks like I will have to make a test version of the procedure, and use an editor to globally substitute '#' with 'temp_'.
    I guess that's not too bad, although it would get painful for a stored procedure called from other procedures - luckily that's not the case for me.
    Of course the test version cannot be used in production, as it will fail if two instances run concurrently.
    The other method I suppose would be to have a "permanent" temp table, and have a column that identifies the instance (with suitable indexes), and create/delete instance records as required. That's fairly messy, and might be less efficient, I'm not sure. Has the advantage that during debugging you can leave the records undeleted on completion to see what happened. Hmm..

Share This Page