Avatar of YZlat
YZlat
Flag for United States of America asked on

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?
Microsoft SQL Server 2008Microsoft SQL Server

Avatar of undefined
Last Comment
YZlat

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Steve Wales

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
YZlat

ASKER
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?
Brian Crowe

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
Steve Wales

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
YZlat

ASKER
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