CODEWITHSUNDEEP
sqlsql-server-2005try-catch
Leandro Bardelli
Leandro Bardelli

Reputation: 11578

Try Catch SQL Server 2005 with ALTER TABLE

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

Upvotes: 1

Views: 816

Answers (1)

Royi Namir
Royi Namir

Reputation: 148524

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;

Upvotes: 2

Related Questions