Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Posts
    54

    Unanswered: Amending Stored Procedure

    I have a stored procedure which creates / drop indexes on tables.
    This SP is referenced by a External Program which populates the tables in the database.

    I have been getting errors saying that the indexes do not exist and therefore cannot be dropped.

    The procedure loops through a table (tbl_indexconfig) which has the table names and index names stored in it and applies these to the given table.

    Below is the SP, can it be altered so it checks if the index exists and only drop's it if it does?

    Code:
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO
    
    --EXEC spCreatedropIndex 'Reference Data','D'    
    ALTER PROCEDURE [dbo].[spCreatedropIndex]    
    @FeedName Varchar(75),  
    @indexflag  char(1)   
    AS    
    SET NOCOUNT ON  
      
    DECLARE @tableName varchar(75),    
            @Indexcolumn Varchar(50),    
            @indexName Varchar(50),    
      @sqlQry varchar(500),    
      @count int ,  
            @IsDisable int,  
      @Sysindexname varchar(50)   
        
    SELECT @count = COUNT(*) FROM tbl_indexconfig    
    WHERE feedName =@FeedName    
        
    WHILE @count>0    
    BEGIN    
     SELECT @tableName = tableName,@Indexcolumn = Indexcolumn,@IndexName = indexName    
     FROM tbl_indexconfig     
     WHERE feedName = @FeedName and FeedID = @count    
    
     SELECT @Sysindexname = [Name],@ISDisable = IS_Disabled   FROM     SYS.INDEXES    WHERE    INDEX_ID = 1 and  OBJECT_NAME(OBJECT_ID) = @tableName AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1    
    
      IF @Sysindexname IS NULL and @indexflag = 'C'  
         SET @SQlQry = 'CREATE CLUSTERED INDEX '+ @IndexName +' ON '+ @tableName+'('+@Indexcolumn+')'    
      ELSE IF @indexflag = 'D' and @Sysindexname IS NOT NULL   
         SET @SQlQry = 'DROP  INDEX  '+ @tableName+'.'+ @Sysindexname   
      
      print @SQlQry  
       EXEC(@SQlQry)    
       SET @Sysindexname = NULL
      SET @count = @count -1    
    END    
      
    SELECT 0

  2. #2
    Join Date
    Jan 2006
    Posts
    54
    Anyone?

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try

    Code:
    SELECT *, [Name]
    FROM     SYS.INDEXES    
    WHERE    INDEX_ID = 1 and  
    	Name = @tableName AND 
    	OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •