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:

CREATE PROCEDURE ChildProc

REQUIRES #SomeTempTable SomeTableType

AS

…

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

CREATE PROCEDURE ChildProc

REQUIRES #SomeTempTable TABLE (Table definition here)

AS

…

I’d love to hear what you think.

2012-01-05