Error handling in a stored procedure

In my database i have a temp table that contains index information, including database name, schema name, table name, index name, index fragmentation percentage

based on fragmentation percentage I either rebuild or reorganize the index using the following code:




-- reorganize or rebuild indexes
	SET @sSQL = ''

	SELECT @sSQL = @sSQL + 
           CASE WHEN FragPercent > 30 THEN
		' ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
		+ QUOTENAME(DatabaseName) + '.'
		+ QUOTENAME(SchemaName) + '.'
		+ QUOTENAME(TableName) + ' REBUILD WITH ( SORT_IN_TEMPDB = ON, ONLINE = ON );'
            ELSE
                ' ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
		+ QUOTENAME(DatabaseName) + '.'
	        + QUOTENAME(SchemaName) + '.'
                + QUOTENAME(TableName) + ' REORGANIZE;'
            END
                
	FROM ##IndexFragmentation
	WHERE (FragPercent <= 30) OR (FragPercent > 30 AND [IsComplex]=0)
	--WHERE FragPercent > 5

	SELECT @sSQL

	EXECUTE sp_executesql @sSQL

Open in new window


The issue here is that if at some point in time one of the indexes is removed before this code it executed, that would throw an exception and my stord procedure would crash. So I wanted to add an error handling in the form of try catch to trap the error. What I need help with is how to ensure that whenever the error is trapped, the code continues to run for all the remaining indexes.

Can anyone help?
LVL 35
YZlatAsked:
Who is Participating?
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
You'd need to save of the schema / table and index name in another variable while building @sSQL

But then you could do something like

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID([@Schema].[@Table]) AND name = '@Index)
  EXECUTE sp_executesql @sSQL  

Also, unless you're just showing us a snippet of the code, I don't think that code is going to work like you think it does.

I assume you have a loop set up to populate that temp table and then return the rows one at a time and process them one at a time ?
0
 
YZlatAuthor Commented:
Yes, you assume right. I have a loop setup that loops through all the databases on the server and retrieves infor about them and the indexes and puts it into the temp table.

Could you make a suggestion on how do I save database name, schema name and index name in another variable and how do i incorporate it into my code?
0
 
Brian CroweDatabase AdministratorCommented:
Not to rain on your parade but there are some really good scripts out that that do this already.

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
0
 
Steve WalesSenior Database AdministratorCommented:
The Ola Hallengren scripts are definitely an excellent set that do the same thing (and more).

If you're doing it from a learning perspective:

After: SET @sSQL = ''

Add something like:

SELECT @Schema = SchemaName, @Table = TableName, @Index = IndexName
From ##IndexFragmentation
etc

You've already defined the column names in that table by the looks.  You'd need to declare the variables higher in your script (like you defined @sSQL somewhere)

Don't re-invent the wheel if you don't have to, but certainly look at the link BriCrowe gave, there's some excellent stuff there.
0
 
YZlatAuthor Commented:
sjwales, your cod is not completely correct:

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID([@Schema].[@Table]) AND name = '@Index)

Open in new window


 gives errors, it should be:

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(@Schema + '.' + @Table) AND name = '@Index)

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.