Solved

Error handling in a stored procedure

Posted on 2014-01-10
5
227 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Results to Excel File 18 73
SSIS GUID Variable 2 30
SQL Add Parameter in Variable 4 21
SQL Query Returns Records in SSMS but not Classic ASP 5 18
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

740 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