TSQL success or failure result of EXEC dynamic SQL Based on the following code:
/* This SP takes a query and a unique temporary table name as input... 
The outputs is a string with the command to create the tmp table based on the query that is given in the @SRC parameter 
*/ 
EXEC dbo.createTmpFromQuery @SRC,@uniqueTMP,@createTmpStr=@SQLStr OUTPUT
/* This executed the query that will create the tmp table and insert the data from @SRC */
EXEC(@SQLStr)

The whole stored procedure that calls these 2 lines (among many others) is in a Try Catch.
I've just run into a problem where the query in @SRC had a syntax error which called the Catch portion:
BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @ERRORMESSAGE=CASE WHEN @ERRORMESSAGE='' THEN ERROR_MESSAGE() ELSE @ERRORMESSAGE END 
        RAISERROR (@ERRORMESSAGE,16,1)
END CATCH

the Try has many validations and I set @ErrorMessage to personalized messages in most cases.
In this case, I would like to set a personalized message instead of getting the standard error message
(example): Incorrect syntax near 'FROM'.

I'm trying to figure out how I can get the EXEC(@SqlStr) return a successful or failure code, so I can set the @ErrorMessage before it goes into catch portion of the code.
I found the solution...
I wrapped the statements in another try catch within the big try catch
(Didn't know we can do that) :-(
    BEGIN TRY 
        EXEC dbo.createTmpFromQuery @SRC,@uniqueTMP,@createTmpStr=@SQLStr OUTPUT
        EXEC(@SQLStr)
    END TRY
    BEGIN CATCH 
        SET @ErrorMessage='Error Creating '+@uniqueTMP+' table - verify query in @SRC'
        RAISERROR(@ErrorMessage,16,1)
    END CATCH 

I tested it an it worked.
I hope this will help someone else...