SQL 2005 Error Handling

[Aidy680]

I'm trying to implement some simple error handling into my (simple) proc and have been advised to use the @@TRANCOUNT method.

My code is as below:

ALTER PROCEDURE [dbo].[spTestSystem1]

@ErrorCode int OUTPUT

AS
BEGIN TRAN
BEGIN TRY

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

INSERT INTO tblTestSystem1
([Date],
[Field1],
[Field2])

(SELECT [Date],
[Field3],
[Field4])

FROM vwAnotherView)

END TRY

BEGIN CATCH
IF @@TRANCOUNT >0
BEGIN
PRINT @@TRANCOUNT
ROLLBACK TRAN
SET @ErrorCode=1
PRINT @ErrorCode
END
END CATCH

IF @@TRANCOUNT >0
BEGIN
COMMIT TRAN
PRINT @@TRANCOUNT
SET @ErrorCode=0
PRINT @ErrorCode
END


Normally it runs fine but I need to break it to make sure that the trapping is working OK.

To do that I renamed the View in my system and then re-ran it.

The first error I got was expected:
"Invalid object name"

However the second error has confused me:

"Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing."

Is this the best way of implementing error handling in a simple proc?

 

[jbenson001]

This is the structure I use in my SPs that use tranactions and try .. catches..

BEGIN TRY
   BEGIN TRANSACTION   
   
      --Your INSERT code here...

   COMMIT TRANSACTION

END TRY
BEGIN CATCH
      ROLLBACK TRANSACTION
END CATCH

 

[Aidy680]

Thanks for your speedy response.

I must admit that I'm not even that familiar with the 'TRY'/'CATCH' methods either.

I assume that it tries to run the code within the 'TRY' operators and if it fails, then it jumps to the 'CATCH' section? Is this correct?

Does it then follow that if a transaction is commited the code jumps over the CATCH section?

 

[bborissov]

Yes.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Take a look at blogs in this thread

http://social.msdn.microsoft.com/Fo...l/thread/f005d1a4-0933-4238-ae8c-7e33b866dadc

 

[Aidy680]

Great stuff chaps.

I'm gonna try these various methods tomorrow and I'll let you know how I get on.

These forums are great!

 

[Aidy680]

OK this is what i found:

The code above works fine if you want to trap data errors but not if you're looking to trap, for example, a change in the name of a view.

I've done some digging and the link below seems to support this idea.

It doesn't seem as though this can be fixed (easily) so I've decided just to live with it instead.

Thanks again for everyone's help.

http://sqlserverpedia.com/wiki/Error_Handling_-_Overview

 

[jbenson001]

You original post was about trapping errors, not finding when a viewname, etc., has changed. These are 2 very different things. An error is an error, a name change is not. What you are looking for is DDL triggers. Look them up in BOL or on-line.

 

[Aidy680]

jbenson yes my original post was to do with trapping errors within a proc. Errors, for example, that can be generated when a view is accidentally renamed and called by a proc.

What came to light through the course of my investigations is that some errors (namely data) can be captured whereas others (renaming of objects) cant - at least not by employing the methodology above; unless someone can tell me different.

That's not to say I dont appreciate your assistance - I do.

 

[jbenson001]

You are correct, in a TRY ... CATCH in a sql SP, you cannot catch that kind of error. If you are using a front end-application, such as .NET, which also employs TRY .. CATCH statements, you can catch those types of errors.
If you are only concerned with someone running sql diretlcy from SSMS, then you may want to track the changing of dB objects' sturcture, names, etc with DDL triggers, but that might get very clumsy and could slow performance.

 

[Aidy680]

Valuable advice.

Thanks again.

 

[gmmastros]

You should be able to check if the view exists before running the code.

Something like this:

ALTER PROCEDURE [dbo].[spTestSystem1]

@ErrorCode                         int OUTPUT

AS
[!]If Not Exists(Select * From Information_Schema.Tables Where Table_Name = 'vwAnotherView' And Table_Type = 'View')
	Return - 1
[/!]
BEGIN TRAN
BEGIN TRY
    
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
    SET NOCOUNT ON

    INSERT INTO tblTestSystem1
                                                    ([Date],
                                                    [Field1],
                                                    [Field2])

    (SELECT                                            [Date],
                                                    [Field3],
                                                    [Field4])
                                                    
    FROM                                            vwAnotherView)

END TRY

BEGIN CATCH
    IF @@TRANCOUNT >0
        BEGIN
            PRINT @@TRANCOUNT
            ROLLBACK TRAN
            SET @ErrorCode=1
            PRINT @ErrorCode
        END
END CATCH
    
    IF @@TRANCOUNT >0
        BEGIN
            COMMIT TRAN
            PRINT @@TRANCOUNT
            SET @ErrorCode=0
            PRINT @ErrorCode
        END

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Just to clarify, I think you should also have error handling in your front end.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom