calling a temp table from within a sproc

Question

calling a temp table from within a sproc

winston Smith

SSCoach

Points: 19500
More actions
January 15, 2010 at 4:03 pm

#218289

I have a sproc that creates a local temp table and populates it.
I call this sproc, and am trying to query this table from outside the sproc. I can call the table, sql returns zero rows from the temp table.
If i create the temp table from my query window, and then call the sproc next, the sproc populates the temp table and i i can select all the rows in the temp table.
eg-- using really dodgy pseudo code. the below sequence does not work for me
create sproc testSproc
(
create table #temptable
(col1 int)
insert into #temptable
select num from tableB
)
--in my query window
exec testSproc
select * from tempTable
--0 rows returned
--the below sequence works
create sproc testSproc
(
insert into #temptable
select num from tableB
)
--in my query window
create table #temptable
(col1 int)
exec testSproc
select * from tempTable
--65 rows returned
why would solution 1 not work, but the 2nd solution work? the parameters for both are identical.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

its variable scope;

anything like variables or temp tables created during the execution of a stored procedure do not exist outside of the proc...that's why you can call the same proc concurrently...100 instances calling the same proc creates 100 unique temp tables, all within their own scope of work; they don't overlap or refer to each other by design.

in your second example, you created the temp table, and did not create a new temp table inside the proc, so it uses the table from the calling code of your session.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Now if you desire to have your first batch of T-SQL function, that is create a temp table, in one connection and query it from another connection, create a GLOBAL temp table (##TempTable). As long at the connection which created the ##TempTable is open you can query it from another connection. To see an example of this go to the Question Of the Day (QOD) at:

http://www.sqlservercentral.com/questions/T-SQL/68937/

Be sure to read the referenced document, and the instructions contained in the QOD T-SQL code, and all the comments that were made about this particular QOD.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

winston Smith SSCoach Points: 19500 More actions · Answer 3

i thought though, that as im calling the sproc from my session, the table created in the sproc is created in my local session, and so i should be able to see and use it. i didnt want to use a global temp table, although i had considered it.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

bitbucket-25253 (1/15/2010)
Now if you desire to have your first batch of T-SQL function, that is create a temp table, in one connection and query it from another connection, create a GLOBAL temp table (##TempTable). As long at the connection which created the ##TempTable is open you can query it from another connection. To see an example of this go to the Question Of the Day (QOD) at:
http://www.sqlservercentral.com/questions/T-SQL/68937/
Be sure to read the referenced document, and the instructions contained in the QOD T-SQL code, and all the comments that were made about this particular QOD.

Not necessary, Ron. If you create the temp table in proc 1 and populate the temp table in proc 2 which is called by proc 1, proc 1 will be able to see those populated contents from proc 1.

The bad part about using a global temp table is that only one instance can be created at a time because, well... it's "global".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Winston,

Try doing it as Jeff has just explained. Create multiple procs, from one of which you will call the other. I think that will probably satisfy the issue for you.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

winston Smith SSCoach Points: 19500 More actions · Answer 6

CirquedeSQLeil (1/15/2010)
Winston,
Try doing it as Jeff has just explained. Create multiple procs, from one of which you will call the other. I think that will probably satisfy the issue for you.

ok, cool. thanks for that. so my next question is:

why can i query a temp table thats created in a sproc, from a sproc calling the first sproc, but i cannot query a temp table created in a sproc when i try to query the table from a query window where ive just executed the 1st sproc, but the query analyzer does not tell me the temp table does not exist? I would assume if i cant query it, i cant see it, and sql should tell me the table doesnt exist.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

winston Smith (1/17/2010)
CirquedeSQLeil (1/15/2010)
Winston,
Try doing it as Jeff has just explained. Create multiple procs, from one of which you will call the other. I think that will probably satisfy the issue for you.
ok, cool. thanks for that. so my next question is:
why can i query a temp table thats created in a sproc, from a sproc calling the first sproc, but i cannot query a temp table created in a sproc when i try to query the table from a query window where ive just executed the 1st sproc, but the query analyzer does not tell me the temp table does not exist? I would assume if i cant query it, i cant see it, and sql should tell me the table doesnt exist.

The easiest way to describe that might be:

Inner can see outer, but outer cannot see inner.

In other words, If I create something within a process context, and then I go call a sub-process (so a process running within another process, so to speak), the inner process see those things/objects/etc... that were available at the time it was started. On the other hand, the temp table didn't exist at the time the outer process started, and it isn't the one creating it, so it has no reason to believe it exists.

There are lots of reasons why it might have been built this way, but suffice it to say - this was built this way on purpose (by design).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Heh... I'll also ask, why ask why? I've been frustrated many times in the past by certain things in many products. I've gotten to the point where instead of pulling my hair out by what I think are certain insanities, I just go with the flow now. While it's true that I have gray hair, at least I still have hair because I know there's always a work around. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)