create table MyTable (id int)
go
Create Table #Test (id int)
go
create proc MyProc
   @id int
as
  insert MyTable select @id
  insert #test select @id
return
go
exec MyProc 2
go
select * from #test
go
drop table MyTable
drop procedure MyProc

I saw a question recently about temp tables and when I was answering it, I learned something. With this code:

create table MyTable (ID int)
go
create proc MyProc
   @id int
as
  create table #test (id int)
  insert MyTable select @id
  insert #test select @id
return
go
exec MyProc 2
go
select * from #test
go
drop table MyTable
drop procedure MyProc

Do you expect the SELECT to return anything? Will the temp table be in

scope? It's an interesting question, and I would hope most of you would

know the answer if you're coding in T-SQL. What about this code?

Does the SELECT return data? The answer to the first is "no" and the second "yes". The reason is scope. While a local temp table (single #) is visible to your entire session, which means multiple statements, it isn't always in scope.

I had to think back to basic computer science and stacks as to how this works. In the first set of code, the variables and temp table that are used in the procedure are created and put on the stack of data while the procedure is being executed. That means that as long as the procedure is being executed, those items can be accessed.

Once the procedure ends, they are popped of the stack and discarded. So that the "select * from #test" returns an error.

In the second set of code, the temp table is put on the stack when it's created. When the procedure is executed, it gets more values on the stack for it's variable, but the temp table is still on the stack. When the procedure ends, @id is lost, but the temp table remains.

The values aren't really placed on the memory stack locations. Rather their references are, with the temp table living in tempdb (or memory) and the variable in memory as well. The stack used for tracking scope is lots of complicated, I'm sure, than what we dealt with in basic Pascal and C back in high school or college, but the theory remains the same.

You have to know your scoping rules when programming, or you might easily get into trouble and spend a lot of time debugging things that should be simple to understand.