Stored Procedure Contracts and Temp Tables

Temp tables are visible within the scope where they are declared but also in sub-scopes. This means that you can declare a temp table in one stored procedure but access it in another stored procedure that is executed from within the first stored procedure.

There are two reasons that people do this. One reason is basically sloppy code, a bit like having all your variables global in a high level programming language. But the more appropriate reason is to avoid the overhead of moving large amounts of data around, and because we only have READONLY table valued parameters.

But I've never liked the idea of this type of access. It breaks normal coding rules on encapsulation. Nothing in the child procedure gives any clue that it expects the temp table to already be present. The code that accesses the temporary object could just as easily be a typo. Where this comes to light is when you try to use tools like SQL Server Data Tools (or previously a similar issue with DataDude). How can a utility that analyzes your code know if that reference is valid or just an unresolved reference?

I've previously posted about the need for stored procedures to have contracts:

This seems to be another case where a contract might help. The fact that a procedure depends upon the pre-existence of a temporary object should be discoverable from the metadata of the procedure. Perhaps something like this:


REQUIRES #SomeTempTable SomeTableType


or if the temp table isn't based on a table type, just syntax similar to a table valued function where you could say:


REQUIRES #SomeTempTable TABLE (Table definition here)


I'd love to hear what you think.

PS: I've added a Connect item on this. Please vote if you agree:

16 thoughts on “Stored Procedure Contracts and Temp Tables”

  1. I would rather have updateable TVPs with statistics – that would solve all my problems in one consistent way.
    Having many similar things that solve essentially one and the same problem does not make much sense to me; it just increases complexity…

  2. Good idea.  I need to maintain compatibility with SQL 2005 still for the moment so we haven't used table parameters (although I plan to drop support shortly after SQL 2012 ships – 3 versions back for an ISV is reasonable in my opinion and SSRS 2008 is soooo much better!)
    Anyway, could table parameters help here?  I don't know how well they perform (similar pros/cons to table variables perhaps?)
    We've used the #temptable trick a couple of times in some stored procs but try to avoid it.  The other way of getting around it is to have a permanent scratch table with a composite key – one of the key's columns being some sort of session/batch identifier.

  3. Good idea but still doesn't provide enough information for syntax analysis.
    How about:-
    DEPENDS_ON SomeProcName(SomeTableName)
    The tooling could then easily find the dependency declared in the contract.

    Neale NOON

  4. I cannot agree more. I do not like table variables because of lacking statistics or non unique indexes etc but I double wish they will create a special type (not temp table and not table variable) for this purpose this way whenever that type is created (ugly but maybe #@table ???) you know that's the only one which can be passed into a procedure (just threw it out there, just like to see declarations more clear than it is today).
    I thought about a table type (in my dream months ago), since we have to do a lot of parallel staging loading into generated table names and then run some processes against to these tables, cannot do it without dynamic sql, therefore I thought about having a table type you create your table as, you an only change the name and maybe add remove indexes to it, this way you can pass the new table name into the procedure and the procedure processes, very similar to the table type except it's actual table, it's only there to verify column structure within a table and guaranteeing the schema (not guaranteeing indexes, keys etc therefore recompile must be maybe mandatory)
    Again could not agree more and cannot keep adding more to it

  5. Hi Neale,
    I like the idea of showing where it's defined but I've also seen situations where people have multiple parent procs that can call the same child proc. Not sure that you could limit it to one.

  6. I would like to see temporary (#-named) tables and procedures go away completely; they are just a backward-compatibility headache with weird semantics.
    The replacements would be: a full implementation of table variables (starting on the lines Alex suggests); and something similar to the module-level table expressions Erland promotes as a superset of temporary views and functions.

  7. Greg,
    That's not the SQL Server we've all come to know and love 😉
    I assumed the scenario where the child procs were actually dependent, but you seem to indicate utility child procs that could reference any suitable temp table (variable names but fixed schemas).
    What you really need is a stored procedure parameter that represents a table pointer (and possibly an expected schema) that resolves to the actual temp table at runtime. This would represent a real chore for the tooling:-(

    Neale NOON

  8. Hi Greg,
    Although your suggestion is a good one I've decided that its not one that I can vote on because, IMO, it would only encourage something which I see as a bad coding practise. That is, use of temp tables that are instantiated in other stored procs.
    Like Alexander I would much rather the product team channel their energies into updateable TVPs rather than supporting antiquated development practises.
    P.S. I'll add this comment to the Connect submission!

  9. Hi Jamie,
    I agree that I wish the existing syntax was never supported in the first place and that better options existed that looked more like real parameters and supported proper encapsulation. However, I can't see the way it works changing in the near future as it would break just way too much code. In that case, I think we need to find a better way to support it in the meantime.

  10. Paul,
    Digressing slightly, I've discovered a situation where I've found temporary procedures to actually be quite useful. That is, encapsulation of code that is to be used in scripts that are called using sqlcmd's :r syntax.
    I need to blog it at some point – its on the blog backlog. 🙂

  11. P.S. I can't comprehand why people (3 at the time of writing) are willing to take the time to leave a comment here agreeing with Greg's idea but can't be bothered to click through to his Connect submission to vote it up (at the time of writing it only has one vote and that will be Greg's).
    Why are people so averse to using Connect? Its a problem.

  12. Hi Jamie,
    I can't say I'm familiar with :r or how it might be used with temporary procedures, so I will look out for your post on that.  I should try not to prejudge, but I can't help thinking that whatever use you have found for this could be done just as well, or perhaps better, with something more satisfactory than a temporary procedure (though these generally irk me much less than other types of #temp object).  Hmm, looks like I am prejudging a bit after all.  Ah well.
    As far as Connect is concerned, as I remarked to you on Twitter, using Connect is often just a horrible experience, so I guess it's really no great wonder that it is not all it could be from a community feedback and participation perspective.

  13. Jamie,
    I clicked on that Connect link, clicked on login, and it froze for a long time. After a minute or so I stopped waiting. Eventually, I got back to it a few minutes later, only to discover that, unlike stackoverflow, I cannot login with gmail.
    A small stackoverflow team with a presumably limited and small budget raised the bar considerably for everyone else.
    If Connect team want us to visit their site rather than stackoverflow, they need to improve their site.

  14. I guess this would be a nice to have, but not a big enough problem to be really necessary.
    I have always found that keeping temporary objects as physical objects in a separate schema (and possibly a separate database) makes developing and debugging much easier. And when production issues arise you can debug through the temporary schema and see the trail of data that existed when the error was generated. Something you cant do if you utilize temporary tables to the extent that seems required to ask for a feature like this.

  15. I use this technique in a number of places where I have utility-type procedures that can be used by themselves or to feed other procedures.  For example, ProcA calls ProcB to gather a set of data and do further manipulation.  ProcA creates the temporary table and ProcB populates it.  ProcB checks for the existence of the temporary table and creates it if it does not already exist. PrcoB is called by ProcA and by other processes that that need the same data but don't need the further processing that ProcA does.
    The downside is that when the definition of the temp table changes, all locations must be syncronized.  I have not found a method to save the table definition in a common place and use it.  One possible way to accomplish that is to create an empty permanent table with the correct definition and then execute SELECT * INTO #Temp FROM Permanent WHERE 1=2", but that method leaves empty tables scattered around the database.  Creating dynamic SQL to create a table does not work because the executed SQL is in a different scope.
    In OO languages, we have the ability to define a class and then instantiate it wherever it is needed.  Having that abillity in T-SQL would be helpful.  Having a "library" of reusable structures in the programming space, not just on the data storage level, would allow us to reuse those structures.  Essentially what I am suggesting is the ability to create a user-defined data type that is a table structure rather than a base data type.
    CREATE TYPE BirthdayList (ID INT, Name VARCHAR(50), BirthDate DATE)
    Then the type could be used where needed.  If a procedure needed that definition for a local variable, it would be declared:
    DECLARE @SendCardsTo  BirthDayList
    We could also use that as a procedure parameter:
    CREATE PROC ProcB @ParamA  BirthdayList = NULL
    Using the above definition, if the parameter was passed, the table/variable would have the data supplied by the caller.  If the parameter was not passed, the table/variable would contain no rows.  Using the first example, ProcA would declare the @SendCardsTo variable and then: EXEC ProcB @ParamA = @SendCardsTo

Leave a Reply

Your email address will not be published. Required fields are marked *