Use of Try Catch block

Question

What is the real use of Try..Catch.. Block
The block catches the error which occurred in the Try block.  How do i make the proc, such that the proc should not succeed, if an error has been caught in the Catch.. block.

Example : I have a proc which inserts record to a table, which has foreign key constraint.  The Catch block catches the error, if a record is no inserted properly.  But, the result says 'commands executed successfully'.  How to make the proc not to succeed.

Thanks.

---

NSG12

Answer 1

See sample here (and a related link) http://blogs.lessthandot.com/index.php/WebDev/WebDesignGraphicsStyling/how-to-insert-information-into-multiple- of how I RAISERROR and handle transactions.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 2

By using RAISERROR statement.

---

With best regards, Yasser Zamani

Answer 3

You will  have to use RAISEERROR in your CATCH block to return the exception back to your application or called of the sp

BEGIN TRY

    -- RAISERROR with severity 11-19 will cause execution to
    -- jump to the CATCH block.

    RAISERROR ('Error raised in TRY block.', 16,1    );

END TRY
BEGIN CATCH

    DECLARE @ErrorMessage NVARCHAR(4000);

    DECLARE @ErrorSeverity INT;

    DECLARE @ErrorState INT;

    SELECT

        @ErrorMessage = ERROR_MESSAGE(),

        @ErrorSeverity = ERROR_SEVERITY(),

        @ErrorState = ERROR_STATE();
    --Log exception in a SQL table
 

    -- Use RAISERROR inside the CATCH block to return error

    -- information about the original error that caused

    -- execution to jump to the CATCH block to the caller (i.e. client app)

    RAISERROR (@ErrorMessage, -- Message text.

               @ErrorSeverity, -- Severity.

               @ErrorState -- State.

               );

END CATCH;