exception handling question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

exception handling question

Author

Topic

chadbryant5
Starting Member

32 Posts

Posted - 2008-04-01 : 13:55:35

I have a question about exception handling in SQL 2005. I'm new to being a DBA and come from a .NET development background. I see that in SQL 2005, tsql has a try/catch support which is nice. I don't see a "finally" block though so I'm trying to understand how this works.

Let's say I have a cursor (I know cursors are to be avoided when possible but this serves as a good example).

Say I have the following and an error occurs causing the catch block to execute and the raiseError happens, does the close and deallocation of the cursor ever happen? Is there a better way to write this if not to ensure the cursor isn't left open? Maybe this isn't an issue like it is in C# if I leave something like a DataReader open which ties up connections from the pool and causes leaks? Will the cursor continue to be looped through if the catch hits or will the raise error stop it and pretty much abort everything else? thanks for the help in advance!!

DECLARE cur CURSOR FOR
Select ID from SomeTable
OPEN cur
FETCH NEXT FROM cur INTO @var
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY

BEGIN TRANSACTION
-- tsql inserts/updates here

COMMIT

END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK

DECLARE @ErrMsg nvarchar(4000),
@ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR ( @ErrMsg, @ErrSeverity, 1 )

END CATCH

FETCH NEXT FROM cur INTO @var
END

CLOSE cur
DEALLOCATE cur

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-04-01 : 15:46:37

you can see how it would react by simulating yourself:



begin try
	select 1
	raiserror('yak', 16, 1)
end try
begin catch
	select 2
end catch
	select 3
return

As long as you dont RETURN or re-raise the error within your catch block you can still do the cleanup after. In your case, you would need to either move the fetch within the try (is there a reason its not?), do nothing in the catch, and do all that clean up after (simulate a finally), or check if the cursor is open within the catch and close it there before raising the error again.

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources