MS SQL 2005 TRY ... CATCH In Stored Procedures

Hello World!

 I was trying to figure out how to throw a custom error message through a stored procedure in MS SQL 2005 that can be "caught" using the structured exception handling provided in the database. All this while, my major interaction had been with SQL 2000 style stored procedures that use the @@ERROR system flag to check for an error after each statement that may cause an error to be thrown. One needs to define GOTO labels to handle the errors once detected.
So here is what I was trying to do:
1. Initiate a transaction
2. Start executing a batch of SQL statements that may run into potential errors OR may need to throw an error if some conditions are met/not met
3. Catch the error using a CATCH block, rollback the transaction is required
4. Return the error encountered using the inbuilt functions ERROR_NUMBER(), ERROR_PROCEDURE(), ERROR_LINE() and ERROR_MESSAGE()

I managed to create a stored procedure as per my requirements stated above, but the issue was, the error I was trying to “throw” was not being “caught” in the CATCH block. The reason: I did not indicate that my error was severe enough!! A very nice article by Pinal Dave explains what the severity levels indicate. To sum up, a severity less than 16 is treated as informational by SQL server and hence the CATCH block does not get hit.

Here is the basic stored procedure body with the logic that tries to do 1 through 4 I listed earlier:

CREATE PROCEDURE MyStoredProc
@Param1 NVARCHAR(20),
@Param2 NVARCHAR(20)

AS

BEGIN
    -- We don’t need extra results to affect our rowcount
    SET NOCOUNT ON

    -- The following block wraps our SQL statements which may throw error   
    BEGIN TRY
        BEGIN TRANSACTION

        DECLARE @MyUniqID INT

    -- Check if there is a record for @Param1 and @Param2
SELECT @MyUniqID = MyTableKey WHERE Col1 = @Param1 AND
Col2 = @Param2
  
    -- Did we find something?
    IF @MyUniqID IS NOT NULL
        BEGIN
            -- Go on do what ya’ like!
        END
    ELSE
    -- Nope! We did not find a row!
        BEGIN
            RAISERROR (N'Invalid Record. There is not record with the specified %s and %s',
                16, -- Severity,        -- Less than 16 would not be enough!
                1, -- State,
                N'Param1’s column name', -- First argument.
                N'Param2’s column name'); -- Second argument.
        END

        -- If we reach here, everything went to plan
        COMMIT TRANSACTION
        RETURN 0
    END TRY

    BEGIN CATCH
        ROLLBACK TRANSACTION
        SELECT ERROR_NUMBER() AS ERR_NUMBER, ERROR_PROCEDURE() AS ERROR_PROCEDURE ,ERROR_LINE() AS ERROR_LINE, ERROR_MESSAGE()AS ERR_MESSAGE      
    END CATCH

    SET NOCOUNT OFF
END


If you are executing this SP though .NET code using ExecuteDataset(), you may examine the columns of the returned data table to check and take appropriate action in the code as required.

Bon Voyage!!