Examples of substance abuse benefits in games
Max Payne series
Max Payne can take painkillers to regain any lost health. By Max Payne 3, Max's reliance on painkillers has made him an addict.
The protagonists can discover or create a variety of status altering drugs such as Jet, which increases their Action Points, and Psycho, which increases their damage output. Other more mundane drugs include various types of alcohol, which can give boosts to Charisma and Strength at the cost of Intelligence. Every drug also includes a chance of becoming addicted when used, causing the player character to have decreased base stats while not under the effect of said drug.
Condemned 2: Bloodshot
Ethan Thomas has become an alcoholic during the time between Condemned and Condemned 2. His over-reliance on alcohol has made it so he needs it to, ironically enough, better steady his aim with firearms.
Dead Rising series
The protagonists of Dead Rising can drink, amongst other fluids, entire bottles of alcohol to heal themselves -- alcohol often tends to provide the largest amount of health at that. However if the player character drinks too much alcohol within quick succession, they will routinely throw up for a short while, leaving them extremely vulnerable to attack.
Characters in the Yakuza games can drink alcohol to gain access to certain 'HEAT' attacks, with minimal downsides.