1 - instead of using EXEC(@Sql), rather declare your @Sql as NVARCHAR and use EXEC sp_executesql @Sql

2 - wrap the exec in a BEGIN TRY .. END TRY block and trap the @@Error value in the BEGIN CATCH ... END CATCH block (the @@Error value will be passed back to the calling / executing command)