Baudelaire wrote a book on the subject called Artificial Paradises. Haven't read it in a while, but I seem to recall that wine got a glowing review, while he didn't think much of hashish. So it goes.
I myself like coffee or other caffeinated beverages for writing, and I do find that a bit of wine of alcohol loosens me up. Not drunk, though, just mildly tipsy. My experience with other drugs is rather limited, but I can't say anything else I've done helped much on the production end. Source material, though, it is very good for.
I rarely drink while writing. If I do, it's generally because I had a glass of wine with dinner and haven't finished it yet. Or I just fancied having a little whisky. It's sometimes a mistake, actually: I find it difficult to write while tired, and alcohol can have much the same effect. But on the other hand, it's good to relax after a long day, so maybe it helps indirectly?
I did used to get up and make a cup of coffee before I sat down to write (and frequently later on if I hit a bit of a slump), but I stopped drinking it altogether a few weeks ago. I was a little concerned that might affect my writing ability, but as far as I can tell it hasn't made a difference.
For the record, I wasn't really aware of alcoholic writers being a stereotype at all, let alone a common one.
I have the occasional drink here and there, never been to the point where I've been drunk, however. I've never actually written while having a drink, either. However, from what it seems, it doesn't sound like it would help very much with me personally. Everyone I know that ends up drunk can hardly write or type or anything, so I honestly don't know how well I'd be articulating myself under the influence of such things.
I'm not sure alcohol is really a good idea while writing. I rarely ever drink, and I've never tried to write while drunk. Alcohol makes me feel hazy; it doesn't really seem like it's the type of thing that can help me think more efficiently or creatively. I think those write while drunk are the exception, not the rule.
I do find that going out to smoke a cigarette can help me refocus myself while I'm writing.
Marijuana is good for brain storming sessions, but that's about it, and I don't rely on it. It just happens that, sometimes, when I'm lighting up, I'm struck with a brilliant idea for a story or a poem. What ever I come up with will require heavy, heavy editing while sober.
Personally, getting drunk is good for my comedic material, but all other aspects of writing? Nah, it just goes down the drain. I find it hard to grab the pen when I have to make the choice between the whiskey bottle and the pen.
I need stone cold soberity for serious writing. Maybe the occasional gem of an idea can happen, but it's far and few in between.