Obtaining my custom error message from SQL 2000 sp

Question

I have an sp with the following pseudo code...

    BEGIN TRANSACTION
      set @errorLocation='Deleting Permissions'
      DELETE [tblUsrPermissions]
      WHERE
       lngUserID = @lngUserID
      if @@error>0
      begin
       goto roll_back
      end

      COMMIT TRANSACTION
      set @errorLocation='' --clear error messages
      select @errorLocation --return success
    return
roll_back:
    IF @@TRANCOUNT > 0
     ROLLBACK TRANSACTION -- there were errors, rollback
    select @errorLocation

I'm using .NET sqlclient sql datareader, and I am get an exeception in code when calling the ExecuteScalar function - an an error occurs during my delete operation in the sp.

I want to obtain my custom error message instead of the exception. What can I do?

Answer 1

use raiserror to thorw your error to the client. note that depending on the severity of the erorr your raiserror message might never be hit. so for more complete answer provide the original error you get and where do you get it.

Answer 2

If you are using SqlServer 2005 or greater put your code inside TRY block and then call RAISERROR in the catch block