why sp_ExecuteSql output parameter returns null when procedure shows correct value in results pane
You never actually set the value of the output parameter in your query. In order to return the value as an output parameter you need to assign the result of the query to the parameter itself. 

Try this:

set @sql =  'SET @number_out = (SELECT ' +  @NumberFieldName 
                              + ' FROM ' + @table 
                             + ' WHERE ' + @IdFieldName +' = '+ @recid + ')';


Or you could do:

set @sql =  'SELECT @number_out = ' +  @NumberFieldName 
          + ' FROM ' + @table 
          + ' WHERE ' + @IdFieldName +' = '+ @recid;