Catch error message returned from sql server stored procedure at front end

I am writing stored procedure in fol开发者_开发知识库lowing way..

CREATE PROCEDURE spTest
@intCompId      int,
@varCompName    varchar(50)
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY 
    INSERT INTO tblCompanyMaster(CompId,CompName)
    VALUES (@intCompId,@varCompName)
    IF(@@ERROR<>0)
        RAISERROR('Error',10,1)
        SELECT ERROR_MESSAGE()

    IF(@@ERROR=0)
    BEGIN
        COMMIT TRANSACTION
        SELECT 0;
    END
    ELSE
        ROLLBACK TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH
END
GO

I this case if error occurs it will return error message. I am calling this sp from front end with sqlcommand property like oSqlcommand.executeNonQuery(); it will return no of rows affected and zero otherwise. Now what I want is if there is any error arise in stored procedure than the message returned by error_message() should be caught at front end as an exception and because I am writing each exception in text file. How can I catch this message at front end?? My coding style is as below....

try
{

   //Some code

}
catch(exception ex)
{

    //Write exception in text file.

}

I want to handle that message at ex.

Your stored procedure contains a fundamental flaw:

If you execute any statement after examining the value of @@ERROR (or @@ROWCOUNT), it gets reset to zero.

You should always cache in a local variable before continuing:

DECLARE @errornum int
SET @errornum = @@ERROR

If you need rowcount as well, then you must perform in a single statement like so

SELECT @errornum = @@ERROR, @rowcount = @@ROWCOUNT

In addition, you should not need to mix TRY/CATCH and examining @@ERROR

You use TRY/CATCH when you want to handle errors in the stored procedure, for example:

BEGIN TRY
    -- ...
    -- Do Stuff
    -- ...
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

In your C# code, catch SqlException