I've read a lot of slashfic where either getting together with Watson solved the whole "my mind rebels against stagnation" problem, or where Watson finally threatened to leave him unless he gave it up.

I've also read a couple of darkfics where Holmes would only give up cocaine if Watson put out for him. Which, given the canonical end of The Sign Of Four, is actually not that OOC.

I've also seen the idea Holmes got genuinely addicted to it (as opposed to using it in an unhealthy but still recreational fashion) during the three years of hiding from Moriarty's circle, and Watson begged/browbeat/out-deduced him into recognizing he had a problem with it and allowing Watson to help him kick the habit.

Oddly enough, I don't think I've ever read a fic where Holmes willingly approaches Watson to ask him for help getting clean, though.