Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Temp tables and scope

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.

Comments

Posted by David McKinney on 5 October 2009

Hi Steve,

It is an interesting subject.

But there are a couple of things about your example that leave me confused.

1) MyTable - what's it for?

2) Have you got your code sections in the wrong order?

For me, the first returns data, while the second returns an error.

That aside, I recently discovered what you're referring to above (thanks to a SSC article).  I've found it's a usable and useful method to get round the question of how to pass a table as a parameter i.e. you don't have to.  You just build the table in the calling proc and fill it etc. in the sub-proc.

It's not 100% pretty, but it can be very useful for building (more or less) modular code.

All the best,

David.

Posted by Steve Jones on 5 October 2009

The code might be in the wrong order. LiveWriter looks like it uploaded something funny.

MyTable was just in contrast to the temp table. I was using it to test the returns there as opposed to a temp table. I think I'm missing a few SELECTs, so I'll edit this.

Leave a Comment

Please register or log in to leave a comment.