Index Problem

Greetings,

In a SQL Query window (2000) I can create a #temp table and then create an index on the # temp table with this code:

create clustered index IX_UpinNbr on #temp20 (UpinNbr)

go

However when I try to compile the same code within a Stored Procedure, I get the following error:

Msg 208, Level 16, State 0, Line 4

Invalid object name '#temp20'.

I realize that the #temp20 table is not a physical table in my database. Can you put an index on a #temp table within a stored procedure?

Thanks!

Tom

Can you post the exact code you are using?

I wonder if you are altering that temporary table after cteating it.

Code Snippet

use northwind

go

createprocedure dbo.p1

as

setnocounton

createtable #t (

Record# intnotnullunique,

DateStamp datetimenotnull

)

createuniqueclusteredindex #t_Record#_u_c_ix

on #t(Record#)

insertinto #t values(1,'6/14/2007 1:00')

insertinto #t values(2,'6/14/2007 2:00')

insertinto #t values(3,'6/14/2007 2:10')

insertinto #t values(4,'6/14/2007 5:10')

select*from #t

droptable #t

go

exec dbo.p1

go

dropprocedure dbo.p1

go

AMB

|||

Here is the entire Stored Procedure script:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTERPROCEDURE [dbo].[proc_san_prov_hr_rpt] AS

-- Created 06/13/2007 by TLeach, CR# C7095, PDAT 1011 Sanctioned Provider Report - Hospital Roster.

-- Hospital Roster

Select a.temp_tracking_number,

a.TaxIDNumber,

a.Section1011IdentificationNbr,

a.LegalBusinesssName,

a.DBAName,

a.AuthorizedRepFirstName,

a.AuthorizedRepLastName,

a.PhysicalAddressLine1,

a.PhysicalAddressLine2,

a.PhysicalCity,

a.PhysicalState,

a.PhysicalZipCode,

b.UpinNbr,

b.PhysicianName

Into #temp20

From dbo.EnrollmentApplication a, dbo.HospitalRoster b

Where a.StatusID ='6'and

a.SecondaryStatus ='FA'and

a.ID = B.ApplicationID

go

createclusteredindex IX_UpinNbr on #temp20 (UpinNbr)

go

-- Now join #temp20 to the cumulative table to check for matches.

-- #temp21 will have PhysicianName and busname matches.

Select a.temp_tracking_number AS'PDAT Temptracknbr',

a.TaxIDNumber AS'PDAT TaxIDNumber',

a.Section1011IdentificationNbr AS'PDAT Section1011nbr',

b.SSN AS'CMS SSN',

a.LegalBusinesssName AS'PDAT LegalBusinessName',

a.DBAName AS'PDAT DBAName',

a.AuthorizedRepFirstName AS'PDAT AuthorizedRepFirstName',

a.AuthorizedRepLastName AS'PDAT AuthorizedRepLastName',

a.PhysicalAddressLine1 AS'PDAT PhysicalAddressLine1',

a.PhysicalAddressLine2 AS'PDAT PhysicalAddressLine2',

a.PhysicalCity AS'PDAT PhysicalCity',

a.PhysicalState AS'PDAT PhysicalState',

a.PhysicalZipCode AS'PDAT PhysicalZipCode',

a.UpinNbr AS'PDAT UPINNbr',

a.PhysicianName AS'PDAT PhysicianName',

'HR Physician Name'AS'Matched On'

Into #temp21

From #temp20 a,

dbo.[0406Cumulative] b

Where(a.PhysicianName = b.busname)

-- #temp22 will have upin matches. (11)

Select a.temp_tracking_number AS'PDAT Temptracknbr',

a.TaxIDNumber AS'PDAT TaxIDNumber',

a.Section1011IdentificationNbr AS'PDAT Section1011nbr',

b.SSN AS'CMS SSN',

a.LegalBusinesssName AS'PDAT LegalBusinessName',

a.DBAName AS'PDAT DBAName',

a.AuthorizedRepFirstName AS'PDAT AuthorizedRepFirstName',

a.AuthorizedRepLastName AS'PDAT AuthorizedRepLastName',

a.PhysicalAddressLine1 AS'PDAT PhysicalAddressLine1',

a.PhysicalAddressLine2 AS'PDAT PhysicalAddressLine2',

a.PhysicalCity AS'PDAT PhysicalCity',

a.PhysicalState AS'PDAT PhysicalState',

a.PhysicalZipCode AS'PDAT PhysicalZipCode',

a.UpinNbr AS'PDAT UPINNbr',

a.PhysicianName AS'PDAT PhysicianName',

'HR Upin'AS'Matched On'

Into #temp22

From #temp20 a,

dbo.[0406Cumulative] b

Where(a.UpinNbr = b.Upin)

Select*

Into #temp23

From #temp21

Union

Select*

From #temp22

Select*from #temp23

Here is the exact error message when I cry to create:

Msg 1906, Level 11, State 1, Line 2

Cannot create an index on '#temp20', because this table does not exist in database 'PDAT_1011'.

Msg 208, Level 16, State 0, Line 4

Invalid object name '#temp20'.

Thanks!

|||

When the code is compiled, the temp table does not yet exist -since it is created by the SELECT...INTO.

IF you want to create the index, then CREATE the table first.

|||Thanks for the help.|||

Why are you using "go" inside the sp?

...

Select a.temp_tracking_number,

a.TaxIDNumber,

a.Section1011IdentificationNbr,

a.LegalBusinesssName,

a.DBAName,

a.AuthorizedRepFirstName,

a.AuthorizedRepLastName,

a.PhysicalAddressLine1,

a.PhysicalAddressLine2,

a.PhysicalCity,

a.PhysicalState,

a.PhysicalZipCode,

b.UpinNbr,

b.PhysicianName

Into #temp20

From dbo.EnrollmentApplication a, dbo.HospitalRoster b

Where a.StatusID ='6'and

a.SecondaryStatus ='FA'and

a.ID = B.ApplicationID

createclusteredindex IX_UpinNbr on #temp20 (UpinNbr)

...

AMB

|||

Hi Arnie,

Not really, see example.

createprocedure dbo.p1

as

selectcast(1 asint)as c1

into #t

createindex #t_c1_nu_nc_ix

on #t(c1)

select*from #t

droptable #t

go

exec dbo.p1

go

dropprocedure dbo.p1

go

The problem with his code is that he has a couple of "GO" in the script. The sp is altered an inmediately it is trying to create an index in a table that does not exists.

AMB

|||No reason, forgot to take it out. Started as a .sql script...|||

I commented out the 'GO' statements and the Stored Procedure compiled cleanly.

Thanks for the help!

|||

I took out the 'GO' statements and the stored procedure compiled and ran cleanly.

Thanks for the help!

|||Yes, I noticed the GO statements, THAT is why the #temp table is not created. He just created the sproc with the SELECT...INTO. Then when running the CREATE INDEX statement, the #temp table did not exist. -I think I said something about it not in existance.