It wasn't nearly as horrifying as it could have been! I'm making myself ill imagining how it would have played if Ben Edlund had written this; thank the gods for small favors.
I AM SO FREAKING GLAD THAT THEY ACTUALLY ACKNOWLEDGED AND THOUGHT ABOUT THE CONSENT ISSUES, and had both Sam and Dean fully cognizant of them. Because given their track record, I was genuinely terrified that the writers were going to be total moronic assholes, and be all "...Whut?" when called on it. I mean, I'm not thrilled to pieces with the necrophilia issue, but it really isn't in the same league as non-consensual sex with a living person. And as presented here, it isn't even the same thing as necrophilia proper, which is often (in straight men) driven by an inability to deal with a woman who is anything other than *completely* passive. Still not cool, but for me the uncoolness is more about SPN *seriously* needing to stop using women's bodies as sites for working out male angst, rather than, "Jesus Christ, they're excusing rape" -- which given some of the writers (EDLUND), it could very easily have been.
That said, as an illustration of Sam's all-consuming despair, I thought it worked *really* well, and made good emotional sense. He knows it isn't 100% cool, but in his grief and need, he allows himself to give in: Sam isn't normally a "fuck the pain away" guy, but when Jess died, he had Dean -- far more important to Sam in the grand scheme of his life -- propping him up. But now he is *all alone*, with only a demon -- a demon with an "ethically harvested" body who is climbing into his lap -- for company and comfort. (She's a demon he doesn't really trust inhabiting a dead body, so it makes sense that his "OMG everyone I sleep with dies!!" angst doesn't kick in.) A major issue of the show as a whole is exactly how far Sam is willing to go, how much he's willing to compromise his integrity. That's been Sam's character trajectory from the beginning, and definitely mirrors the issue of using his powers. Sam fucking Ruby isn't obviously about saving Dean, but it is absolutely *about* Dean -- it's very, very telling that Ruby gets through to Sam by acting like Dean, and saying things Dean would say. It's made very clear that Sam fucked Ruby because Dean wasn't there -- and yeah, *everything* that implies. I think Dean's response to Ruby has been extremely well-played by Jensen: it isn't just natural antipathy because she's a demon, but flat-out jealousy -- think of the discovery scene in "Metamorphosis," which was set up like a husband seeing his partner in bed with someone else.
On a totally shallow level, Jesus, the hot. And it's interesting how Ruby gets him both by convincing him it's okay, and yet simultaneously reminding him of *exactly* how baddirtywrong it is. Sam gets off on both a) fucking those he shouldn't and b) convincing himself, if only temporarily, that it's okay. Just sayin'. Also, Genevieve Cortese is gorgeous, but I've always found her acting wooden; apparently, all she needed to unleash her talent was a scene that required her to rub herself all over Jared Padalecki. I fully approve.
As for the rest, it was like fanfic come to life, in a good way. Hotel room medicine! Drunken!Sammy! POOL HUSTLING! And of course, the fact that when Sam and Dean were together, the screen was DRIPPING WITH WINCESTUOUS TENSION.
ETA: MALE CROSSROADS DEMON! SAM WAS TOTALLY PREPARED TO KISS HIM.
So, the good was great, and the iffy wasn't as awful as it could have been, and was played to make sense emotionally. *relieved thumbs up*