Error Handling

Question

Error Handling

Adam Sandler

SSCrazy

Points: 2697
More actions
March 9, 2007 at 8:45 am

#117892

Hi I am a beginner and have read up a little on error handling but I don't seem to have found what I want -

In the following sp - how do I get the code to continue to do EXEc('refreshview 'View2') when it has failed on EXEc('refreshview 'View3') ie because view3 isn't there or any other reason it would fail on view3.

Thanks

CREATE PROCEDURE procRefresh2Views
@isql varchar(2000) = null,
@objname varchar(200) = null,
@intCount int = 0,
@Err int = 0,
@intCounter int = 0

AS

SELECT @isql = 'sp_refreshView' + ' ' + '''View3'''

exec(@isql)

SELECT @isql = 'sp_refreshView' + ' ' + '''View2'''

exec(@isql)
GO

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

I think for what you want to do here, this is not really a question of handling the error. What you want to do here is to check for the existance of the object prior to running the refresh.

CREATE PROCEDURE procRefresh2Views

@isql varchar(2000) = null,

@objname varchar(200) = null,

@intCount int = 0,

@Err int = 0,

@intCounter int = 0

AS

SET @objname = 'view2'

SET @isql = 'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('+ @objName + ') AND OBJECTPROPERTY(id, ''IsView'') = 1) EXEC @intReturnValue = sp_refreshview ' + @objName

EXEC (@isql)

SET @objname = 'view3'

SET @isql = 'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('+ @objName + ') AND OBJECTPROPERTY(id, ''IsView'') = 1) EXEC @intReturnValue = sp_refreshview ' + @objName

EXEC (@isql)

GO

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

John Rowan SSC Guru Points: 56440 More actions · Answer 2

On another note, you really do not need to be using dynamic SQL here. I stayed with it for my example, but if you are passing in the object name as an input parameter, just stick with static SQL (unless I'm missing something here).

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Adam Sandler SSCrazy Points: 2697 More actions · Answer 3

Thanks -

Its just an example, as I say I need to carry on if the refresh fails for any reason not just if the view doesn't exist. If I could check to see if it would fail before doing it that would be great. Otherwise I need to handle it after the fact and carry on to try the next EXEC(.........

(The dynamic SQL is needed for other things outside of the bit I'm stuck with here)

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 4

The best error handling method in SQL 2000 is defensive programming that checks everything in advance that you can think of that might cause an error. SQL 2005 gives you much better error handling with TRY...CATCH, but you have to work with what you have at the moment.

If you're using dynamic SQL anyway, you could put multiple commands together with GO in between them, assuming the entire script is less than 8000 chars.

SELECT @isql = 'exec sp_refreshView ''View3''

' + 'GO

exec sp_refreshView ''View2''

' + 'GO'

exec (@isql)

Adam Sandler SSCrazy Points: 2697 More actions · Answer 5

Thanks - it all togther to one EXEC? This will then allow run refreshView ''View2'' even though it failed on View3?

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

Using GO splits the input into separate batches, just like in Query Analyzer. An error in the first batch should not prevent the second batch from executing.

Just because you can do this doesn't mean you should though. If the error with the first command is a condition you can test to avoid causing the error, you should probably do it that way.

vadba SSChampion Points: 11133 More actions · Answer 7

For many errors, SQL Server will continue executing code. It would be up to you to trap certain conditions and then STOP processing. There is no way to cause SQL Server to continue after it aborts, because your code is no longer executing.

Also, nested stored procedures, each execting dynamic SQL, is error-handling hell. The thought of trying to propogate errors back up the execution chain is not pleasant (and not always possible).