Try Catch SQL Server 2005 with ALTER TABLE

Question

I'm trying to alter some tables with constraints and for production environment I need some kind of Try/Catch with "print" functions, not commons sql errores. How can i do this? I tried with this:

BEGIN TRY
My command
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;

but it doesnt work. I changed the table's name to one that doesnt exists and the result was a typical message.

Thanks in advance

My source: http://msdn.microsoft.com/en-us/library/ms178592%28v=sql.90%29.aspx

Royi Namir · Accepted Answer · 2012-02-07 14:42:47Z

2

It does work in my comp

Also , why dont you use print messages ?

enter image description here

edit

According To Microsoft:

this is the pattern where try catch + transaction should be used with :

BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

edited Feb 7, 2012 at 14:42

answered Feb 7, 2012 at 14:18

Royi Namir

149k145 gold badges502 silver badges833 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Leandro Bardelli Over a year ago

but how can i disable the messages?

Royi Namir Over a year ago

@leonardo just remove the RAISERROR line

Leandro Bardelli Over a year ago

Ok, the problem is another one. It's about rollback transaction with catch. When something enter on an error, the next commands fails. My idea was do a begin transaction, send commands, catch error and rollback, or finally and commit

Collectives™ on Stack Overflow

Try Catch SQL Server 2005 with ALTER TABLE

1 Answer 1

edit

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

edit

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related