June 12, 2009 at 7:48 am
Hi,
I have a store proc that execs 2 others before it builds the temporary table that I want to use as the data source in a Crystal Report. The two others also create temporary tables - i.e. these are "side-effects", preventing the use of a table-valued function.
Ideally, I'd have liked to be able to do
CREATE PROCEDURE mySP AS SELECT ....
which would be a good data source. But the single-statement SP above can't have exec'd the two other SPs that create necessary "side-effects" (temporary tables) that this SP needs.
Please help?
Thanks in advance
June 12, 2009 at 7:52 am
Crystal should be OK with your SP, as long as you just put the SELECT... at the end.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 12, 2009 at 10:05 am
Thanks, Barry - but unfortunately, that didn't work. I've had to build the report by using the temporary table directly, rather than using the Stored Proc directly as the data source, even though the last statement of the stored proc is SELECT * from tmpTable .. If I've missed something, please get back to me.
Cheers,
Derek
June 12, 2009 at 10:10 am
Is there a reason why you don't have the logic to create the temp tables in the main SP?
June 12, 2009 at 10:24 am
No reason - but wouldn't that be equivalent?
June 12, 2009 at 10:30 am
Typically, temp tables only last for the length of the session. My understanding is that when you exec sp_[name] then it's essentially running the SP outside of your current session. If instead you run:
create #table as
(col datatype,
col2 ....
)
-- index temp table for ease?
-- create clustered index Index1 on #table (col2)
insert into #table (columns)
Select
col,
col2,
from DataTable
Select t.col,
t.col2,
d.col1,
d.col2
from #table t
inner join DataTable2 d
on t.Col = d.col
-- This will return the data to the Crystal Report
June 12, 2009 at 1:02 pm
epriddy (6/12/2009)
Typically, temp tables only last for the length of the session. My understanding is that when you exec sp_[name] then it's essentially running the SP outside of your current session. ...
Close, just replace "session" with "request" above and you've got it. A request is equivalent to a batch and a stored procedure is it's own batch, so it runs as a separate request.
The session is what your SPID is associated with and it can have many requests, nested (sprocs calling other sprocs) or in sequence (GO's in-between) or in parallel (only with MARS AFAIK).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 12, 2009 at 1:04 pm
Awesome!! Thank you 🙂
June 12, 2009 at 2:16 pm
If you want to work with a temp-table you have to create it outside your procedure. You can fill it within your procedure but if you create a temp table within a SP it will be destroyed when your procedure returns (runs out of scope).
How to Post Data/Code to get the best Help How to Post Performance Problems
June 12, 2009 at 7:45 pm
You know, I've always wondered why the #Temp tables that we create in SSMS (or QA) don't get destroyed when the batch/request ends. Shouldn't they run out of scope and get destroyed also? Why do they survive until the session ends?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 12, 2009 at 8:33 pm
RBarryYoung (6/12/2009)
You know, I've always wondered why the #Temp tables that we create in SSMS (or QA) don't get destroyed when the batch/request ends. Shouldn't they run out of scope and get destroyed also? Why do they survive until the session ends?
Could it be that they are associated with the session that created them, not the request?
Table variables, on the other hand, go out of scope when you end a batch with a GO statement in SSMS.
June 12, 2009 at 8:41 pm
RBarryYoung (6/12/2009)
You know, I've always wondered why the #Temp tables that we create in SSMS (or QA) don't get destroyed when the batch/request ends. Shouldn't they run out of scope and get destroyed also? Why do they survive until the session ends?
Barry, I thought you read my article Comparing Table Variables to Temporary Tables[/url]:
2) Local temporary tables (created with a single “#”) are visible in the current connection, from the point the table is created and inside nested stored procedures. Local temporary tables will be dropped when:
a. Explicitly dropped using DROP TABLE.
b. If it is a local temporary table and it was created inside a stored procedure, it will be dropped when the stored procedure is finished.
c. All other local temporary tables will be dropped at the end of the current session.
So, since a SSMS / QA window is a CONNECTION, the temp table stay around until that connection (aka SSMS query window) is closed.
A Table Variable will run out of scope at the end of the batch. Not a temporary table.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 13, 2009 at 3:17 am
RBarryYoung (6/12/2009)
You know, I've always wondered why the #Temp tables that we create in SSMS (or QA) don't get destroyed when the batch/request ends. Shouldn't they run out of scope and get destroyed also? Why do they survive until the session ends?
Because the execution scope they are created in is your connection (session). A procedure is a capsuled module which has its own scope which extends your session scope. If you create a temp table within a procedure and call a second (nested) procedure the temp table will be available in this nested procedure because its scope extends the calling procedure.
Same behavior as SSMS have temp tables created by client side code. They are available until you close the connection.
How to Post Data/Code to get the best Help How to Post Performance Problems
June 13, 2009 at 12:00 pm
Uh, OK folks. Yes, I did know that when created in a top-level request, the temp tables scope is attached to the session instead of the request. (And it is the session and NOT the connection, pooled connections wouldn't work otherwise). The question that I was asking was why was it implemented that way?
And just so it's clear: a Request is the SQLOS container of a single batch. A Batch is one or more SQL commands that are compiled together. That means that a stored procedure is a batch. That also means that a dynamic SQL execution of one or mode commands is also a batch. And the commands that we type into an SSMS query window, in-between GO's become a batch when we compile and run them.
So why does a temp table attach it's scope to the request for these first two types of batches, but attaches to the session scope only for this last kind of batch?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 13, 2009 at 12:46 pm
RBarryYoung (6/13/2009)
Uh, OK folks. Yes, I did know that when created in a top-level request, the temp tables scope is attached to the session instead of the request.
Barry, thanks for this feedback. I must confess I was a little bit confused. :hehe:
How to Post Data/Code to get the best Help How to Post Performance Problems
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply