Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 553
  • Last Modified:

Create index via SQL stored procedure

I am attempting to create a stored procedure which will generate indexes on SQL tables, if they do not already exist
Code is below.

However, I keep getting the error:
Msg 1913, Level 16, State 1, Line 1
The operation failed because an index or statistics with name 'IX_d_StudentsCurrentShortCS_studentNumber' already exists on table 'REAdw.dbo.d_StudentsCurrentShortCS'.

I know that I cannot create an index if the index name alreay exists in sys.indexex, but I am using code to check for it and delete if already there, before creating/re-creating.

Any guidance would be most appreciated

- - - - -
USE [REAdw]
GO
/****** Object:  StoredProcedure [dbo].[spd_cube_transcript]    Script Date: 11/16/2010 10:23:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter proc spd_MakeIndexes
as
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_d_StudentsCurrentShortCS_studentNumber')
    DROP INDEX IX_d_StudentsCurrentShortCS_studentNumber ON REAdw.dbo.d_StudentsCurrentShortCS;
GO
CREATE INDEX IX_d_StudentsCurrentShortCS_studentNumber
    ON REAdw.dbo.d_StudentsCurrentShortCS (studentNumber);
GO
0
JEClark1
Asked:
JEClark1
  • 2
1 Solution
 
EvilPostItCommented:
You are creating the index when you create the stored procedure. GO denotes the end of the stored procedure and the subsequent command is then processed on its own.

Instead do the following.

alter proc spd_MakeIndexes
as
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_d_StudentsCurrentShortCS_studentNumber')
BEGIN TRAN
DROP INDEX IX_d_StudentsCurrentShortCS_studentNumber ON REAdw.dbo.d_StudentsCurrentShortCS;
COMMIT TRAN
BEGIN TRAN
CREATE INDEX IX_d_StudentsCurrentShortCS_studentNumber ON REAdw.dbo.d_StudentsCurrentShortCS (studentNumber); 
COMMIT TRAN
GO 

Open in new window

0
 
JEClark1Author Commented:
Thank you for a quick and concise response!  Worked perfectly.
0
 
EvilPostItCommented:
Glad to be of assistance.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now