May 11, 2009 at 12:18 pm
I have a script similar to the below example in prod
example:
select tt.t1, tt.t2, tt.t3 from test_table tt
inner join #con c
on tt.id = c.id
can someone explain the join on #con (i did not see a create table #con in the script) trying to figure out if this is another way of creating a temp table or is it the the temp table #con was already created and beong refered to.
thanks
May 11, 2009 at 12:25 pm
and this works?
There must be something in that connection / session that creates #con.
AFAIK, an explicit create or select..into is needed.
May 11, 2009 at 12:39 pm
well i'm assuming it works, i configured profiler to track SPs with recompile and this was one of the many SPs it picked up.
the stored proc with the script ex as shown has only 1 input parameter of int type, the rest of it is a basic select statement no (select into or inserts). so i was puzzled to see the temp table in the join.
May 11, 2009 at 2:27 pm
Table could have been created in a parent procedure. This, for eg, will work
CREATE PROCEDURE OuterProc
AS
CREATE TABLE #Temp (id INT)
INSERT INTO #Temp values (1)
EXEC InnerProc
GO
CREATE PROC InnerProc
AS
SELECT id FROM #Temp
GO
EXEC OuterProc
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2009 at 2:42 pm
There must be something else, like what Gail has listed.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply