I would like to combine RegisterExpectedError with additional Asserts.

I have a stored procedure to insert data into a table. Before executing the insert statement, a permission check is done (via a procedure call passing the user name). If a user does not have permission, then an error is thrown and the procedure returns 1.

Apart from checking that the correct error is raised, I would also like to verify that the return status is 1 and that no inserts are performed into the table.

But due to the TRY CATCH mechanism, the ASSERTs are not executed.

How can I achieve this check?

Sample code:

ALTER PROCEDURE dbo.[uta_p_ins_zip_code#NoPermission_ThrowError]
AS
BEGIN

DECLARE @status         int

EXEC TST.Assert.RegisterExpectedError @ContextMessage = 'Throw error if user has no permission',
                                      @ExpectedErrorMessage = 'No permission to insert zip code'

-- Action: Run the sql object under test
EXEC @status = p_ins_zip_code @zip_code   = N'12345',
                              @city       = N'MyCity'

EXEC TST.Assert.Equals @ContextMessage = 'Return status should be 1.',
                       @ExpectedValue  = 1,
                       @ActualValue    = @status

END

For this case I don’t have a perfect solution. The best you can do is to have an optional parameter on the stored procedure that you test (p_ins_zip_code). Let’s say this parameter is called @throwOnError and has a default value of 1. The procedure will throw based on this parameter. It will also return an error code in the error case regardless of @throwOnError. When called with @throwOnError=0  and on error, the sproc will not invoke RAISERROR and only return an error code. When called with value @throwOnError=1 and on error, the sproc will invoke RAISERROR and if its execution continues will also return an error code. The old callers will not have to change since this parameter has a default value of 1.

Obviously this solution is not perfect. It can only guarantee that:
- The sproc throws when called with @throwOnError=1.
- The sproc returns the correct error code when called with @throwOnError=0.
However it will not guarantee that when called with @throwOnError=1 the sproc will return the expected error code. But, If you make sure that inside p_ins_zip_code the RAISERROR branch and the "RETURN errocode" branch are well separated (are independent) then this is not that bad. In any case it is as good as you’ll get for this kind of scenario.