Output Variable Problem With Dynamic SQL + RAISERROR + TRY/CATCH

I have a procedure that executes a sql statement passed in as a parameter. The parameter variable is

@pSqlStatement.


CREATE PROCEDURE [dbo].[ProcedureTest]

@pSqlStatement nvarchar(4000)

AS

BEGIN

DECLARE @retVal int;

BEGIN TRY

EXECUTE sp_executesql @pSqlStatement, N'@ret int OUTPUT', @retVal OUTPUT;

PRINT 'try @retVal = ' + ISNULL(CAST(@retVal AS varchar), 'null');

END TRY

BEGIN CATCH

PRINT 'catch @retVal = ' + ISNULL(CAST(@retVal AS varchar), 'null');

END CATCH;

END;

Here is an example of this procedure being called:


EXECUTE [dbo].[ProcedureTest]

@pSQLStatement = N'EXECUTE @ret = [log].[Log.SetSeverityLevel] 'debug'

Note that in the sp_executesql statement, the @ret variable from the dynamic SQL gets assigned to the local variable @retVal.

Here's the problem; when the procedure [log].[Log.SetSeverityLevel] that gets dynamically executed does a RAISERROR, the code in [dbo].[ProcedureTest] falls through to the catch block in [dbo].[ProcedureTest] as it should, but the value of @retVal is null in the catch block, when the return code in [log].[Log.SetSeverityLevel] is set to -1.

If you comment out BEGIN TRY / END TRY / BEGIN CATCH / END CATCH in [dbo].[ProcedureTest], the value of @retVal is correctly seen as -1. There's something about the TRY CATCH structure that's blowing away @retVal when an error is raised and it falls through to the CATCH block.

Anybody ever seen this before? Is there a workaround?

Thanks - Dana