I am a layman in SYBASE world.
In order to improve performance, I am creating indexes after data load of temporary tables in many stored procedures.
Say, one proc A has an insert into temporary table X. so I have created index IDX after insert statement.
Proc B is invoking Proc A in a loop. In this case, index IDX on temporary table X will try to get created at each call. That’s where I get duplicate index has already create on table X with index name IDX.
To avoid that, I developed code piece(below) to check if the above IDX on temporary table A created or not. If not exists, I create index IDX on temporary table A. This will avoid duplicate index error when invoked proc A in a loop. It's not helping me.
I need to understand how to find if an indexes created on temp table pro-grammatically to restrict duplicate index error.
OPEN CUR_VAR_1 FETCH CUR_VAR_1 INTO @VARIABLE WHILE (@@sqlstatus=0) BEGIN INSERT INTO #TABLE_X SELECT * FROM TABLE Y IF NOT EXISTS(SELECT 1 FROM sysindexes where id=OBJECT_ID('#TABLE_X') and name ='IDX') BEGIN CREATE INDEX IDX on #TABLE_X(COL_1) END End CLOSE CUR_VAR_1
Msg 1913, Level 16, State 1: Server 'ABCDE', Procedure 'PROC_A', Line 1155: There is already an index on table '#TABLE_X' named 'IDX'
A quick help is really appreciated as I have to complete this no later than 04/16/2018.