Solved

Error handling in a stored procedure

Posted on 2014-01-10
5
230 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

737 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