Solved

Error handling in a stored procedure

Posted on 2014-01-10
5
225 Views
Last Modified: 2014-01-22
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?
0
Comment
Question by:YZlat
  • 2
  • 2
5 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 400 total points
ID: 39772355
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
 
LVL 35

Author Comment

by:YZlat
ID: 39772407
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
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39772582
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
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39772606
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
 
LVL 35

Author Comment

by:YZlat
ID: 39801106
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

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question