Very Computer

Please help,
I have a real problem with  SQL 6.0 and SQL 6.5 not  recognize a creation
of an index in a  temporary table when done inside a stored procedure only.
It is okay when done outside in the query window using GO.

try this experiment and you will understand what I am saying.

in the query window type
select * into #temp1 from sysindexes
go
create index temp1_index on #temp1(id)
go
select * from #temp1(index=temp1_index)
go

The above code will run fine.

try to put the code inside a sp, without the go
and run it,
it will give you the following error
Msg 308, Level 16, State 1
Index 'temp1_index ' on table '#temp1' (specified in the FROM clause) does
not exist.

I need to find an answer to my problem, or a work around it
Thank you for hep
Tomas S Polanco
Sanpoco Computer System

A stored procedure is compiled as a single batch before anything is
executed, so when the second select is compiled, the index has not been
created yet. You can circumvent this by running the second select in its
own batch, using the dynamic execute:

select * into #temp1 ....
create index ....
exec ('select * from #temp1(index=temp1_index)')

Hope this helps!
Kalen Delaney