Temp Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Temp Tables

Author

Topic

JeffT
Posting Yak Master

111 Posts

Posted - 2006-04-07 : 12:56:09

Hi,

I have a called stored procedure which creates a bunch of temporary tables, inserts data into them, indexes the tables and then returns to the main calling SP. In the main stored procedure I then do SELECTs from the temporary tables. My problem is I keep getting
invalid object errors on the temporary tables:
Invalid object name '#temp_table1'

The stored procedure is in a test environment. In the SELECT I tried a prefix of database owner (my logon) as well as "dbo." but still get the error. Any suggestions as to what I am doing wrong would be much appreciated.

Thanks,
Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-07 : 12:57:17

Please post the code.

Tara Kizer
aka tduggan

JeffT
Posting Yak Master

111 Posts

Posted - 2006-04-07 : 13:09:44

Ok thanks !

Here's where I create the temporary table:

CREATE TABLE [#temp_TXN_HEAD_837] (
[FILE_AUTH_NBR] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[GS06GRP_CTL_NBR] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SEQ_NBR1] [int] NULL ,
[SGMT_ID1] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOOP_ID1] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ST01TXN_ID] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ST02TXN_CTL_NBR] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SEQ_NBR2] [int] NULL ,
[SGMT_ID2] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BHT01HRCHL_STRC] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BHT02TXN_PRPS_CD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BHT03ORGN_TXN_ID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BHT04TXN_CRE_DT] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BHT05TXN_CRE_TM] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BHT06TXN_TYPE_CD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

insert into #temp_TXN_HEAD_837
Select * from TXN_HEAD_837 a
where a.file_auth_nbr in (select distinct file_auth_nbr_837 from #temp_hdr_ctl_nbr_status)

---------------------------------------------------------------------------
-- Index Temporary file
----------------------------------------------------------------------------
CREATE CLUSTERED INDEX [IX_HF1] ON [#temp_TXN_HEAD_837]([FILE_AUTH_NBR],[SEQ_NBR1],[GS06GRP_CTL_NBR],[ST02TXN_CTL_NBR]) ON [PRIMARY]
CREATE INDEX [IX_HF2] ON [#temp_TXN_HEAD_837]([FILE_AUTH_NBR],[SEQ_NBR1]) ON [PRIMARY]
CREATE INDEX [IX_HF3] ON [#temp_TXN_HEAD_837]([SEQ_NBR1]) ON [PRIMARY]

Here's where I do the SELECT:

Insert into dmapgmr1.prep_supp_837p
select h.tcn ,
h.file_auth_nbr_837,
a.GS06GRP_CTL_NBR,
a.ST02TXN_CTL_NBR,
a.SEQ_NBR2,
RIGHT(h.type,1),
isnull(a.loop_id1,' '),
'000',
a.sgmt_id2,
'000',
convert(char(8),(isnull(BHT04TXN_CRE_DT, ' ')))
from #temp_TXN_HEAD_837 a,
#temp_hdr_ctl_nbr_status h
where a.file_auth_nbr = h.file_auth_nbr_837
and a.seq_nbr2 between h.txn_start_seq_nbr and h.txn_end_seq_nbr

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-07 : 13:15:08

Is this all done in one stored procedure? If not, then (quote from BOL):

quote:

Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.

Tara Kizer
aka tduggan

JeffT
Posting Yak Master

111 Posts

Posted - 2006-04-07 : 13:20:10

No, the table CREATE, INSERT, CREATE INDEX are created in a "called" stored procedure called by the main stored procedure which contains the subsequent SELECT of the temporary table.
Thanks,
Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-07 : 13:23:43

The temp table is not visible outside of the stored procedure in which it is created.

You can use a global temporary table, but it's visible to all active sessions.

If you post your actual code, we might be able to help you better.

Tara Kizer
aka tduggan

JeffT
Posting Yak Master

111 Posts

Posted - 2006-04-07 : 14:30:29

OK, will post code soon, off to a meeting, thanks. I had thought that, at some point in my limited Stored Proc/SQl experience I had done something where the temp table created in the calling SP was visible in the called SP...oh well
Thanks again,
Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources