I've only had the need for WITH RECOMPILE once. I had a stored procedure that would gather data for a report and it was always returning something other than what was expected. I made sure there were not multiples like dbo.stored proc and user name.proc and thus executing the wrong one, ran trace sessions on it and anything else I could throw at it. It was only after adding recompile to it that the thing started to act right. Even drop/create didn't solve the issue. I didn't have a lot of time to spend on the issue so I never found out exactly why this was an issue.