I am thinking about buying Wolfenstein (2009), but on the packaging I noticed that it says something about Steam. Do I also need Steam for single player mode? Basically I want to avoid Steam for various reasons that are irrelevant to this question.
Yes, you need Steam to install the game. I will not run at all without Steam.
Steam is a DRM and content-distribution client, not a multiplayer service. Its main function is to manage the installation of the game, tie the serial number to a single account, and keep it up-to-date. All of these functions are independent of online multiplayer options in a game – in fact, there are many games that use/require Steam that have no online capabilities at all, but even these non-online-aware games require Steam because the Steam client manages them.
For Wolfenstein (2009) in particular, it was released for PC before it was released for Steam (August 2009 for original NA, AUS, and EU releases, October 2009 for Steam release), so in theory there are copies out there for sale that don't require Steam at all. If the one you're considering buying says "Steam" on the packaging though, then that copy is not one of them and it will not run without first installing Steam.