> Now, I'm not promoting this as a good approach, but you could also do
> the following:
> Build the temp table outside of the stored proc. That means it would
> be a 'permanent' table, but you could place it on tempdb by using
> CREATE TABLE TEMPDB..items_ldgr. Then create the index.
> If the first step in the stored proc is a 'DELETE TEMPDB..items_ldgr'
> statement, then you'll start with an empty table each time. Assuming
> that this proc is the only process that will use the table, you should
> have no problems. If more than one instance of the proc is running
> simultaneously, one of the instances will simply block until the other
> process is finished.
Add a spid column to the temp table. And use that as the first item of your
index. Then multiple processes can access the same table without colliding (modulo
page level locks, of course).
> >> I have built a stored procedure in Sybase, that creates a few temp tables. On
> >> one of those tables I have created an index:
> >> CREATE TABLE #items_ldgr
> >> (fund_code char(8) NULL,
> >> fund char(60) NULL,
> >> fund_name char(60) NULL,
> >> currency char(3) NULL,
> >> value_date datetime NULL,
> >> match_no integer NULL,
> >> ref_no char(16) NULL,
> >> description char(50) NULL,
> >> cusip char(9) NULL,
> >> trade_date datetime NULL,
> >> amount Decimal (18,2) NULL,
> >> match_date datetime NULL,
> >> stmt_date datetime NULL,
> >> status char(1) NULL,
> >> gin integer NULL )
> >> CREATE INDEX items_ldgr_idx on #items_ldgr (fund, currency, match_no)
> >> Then I joined this with another table temp table with a similar index. I
> >> did put the index in the join clause so that it would force the index:
> >> FROM #items_ldgr l (index items_ldgr_idx),
> >> When I run this in the Query Planner, it tells me that it cannot find the
> >> index. Are there any steps that I am missing, or am I doing this correctly? I
> >> am approaching this the same way as you would for a normal table index.
> >> Thanks,
> >> Jeff
> >Funny you should mention this problem. I had the exact same problem with
> >this (except my was an update based on the index) and posted to the
> >group. The problem is that the index doesn't exist when the optimizer
> >creates the stored procedure, hence it has no statistics for the index
> >and will do table scans. Move the select that needs the index to another
> >stored procedure (procedure 1 calls procedure 2) and the optimizer will
> >pick it up. I was hoping someone here knew of a better way, but it
> >appears this is the way it has to be done. I hope this helps!
Michael Peppler -||- Data Migrations Inc.
Int. Sybase User Group -||- http://www.isug.com