does this ever work if you would like a hotel room upgrade?
 

If you wanted to get a room upgrade at say, a Marriott, and you had a Marriott Rewards card, would you be likely to be given an upgrade at check in provided there were rooms available? Or does presenting one of these cards make no difference at check in whatsoever?

All of the major hotel chains have frequent guest membership program that have different levels of memberships. One of the perks for the most frequent visitors is the ability to get an upgrade when available. You'll find many high level members (50+ nights a year) who complain about rarely getting an upgrade even though it is one of their stated benefits. In other words, having the card might help, but not much. If there is a lot of upgradeable rooms you are just as likely to get an upgrade for "being nice" as you would for being a member with no status.

I've always gotten an upgrade with my Hilton Honors card.

I agree with the above.
Even with American Express Platinum and Leaders Club membership (Leading Hotels of the World) one of the perks is a guaranteed upgrade if available. I get them about 50-60% of the time.
It ususally boils down to an availability issue.
Even without the above I used to get upgrades maybe 30% of the time just by being nice and asking.

I didn't get an upgraded room but I did get free parking once when I presented by Hilton Honors card. I wasn't even asking for it - I just wanted to get credit for my stay. The free parking saved me $30 - not bad.

More than once we've been upgraded by flashing the frequent sleeper card even when the room had been booked through Priceline. What does it hurt?

I've also been upgraded using my frequent stay card even when I've booked through priceline. I'm sure a couple of times it has not been available but it hasn't been often.

If you were a Marriott Rewards base member with no status, your upgrade chances are probably about the same as a non-member. Technically even silver level members aren't entitled to upgrades, only gold and platinum. I would say unless you have elite status, it really doesn't make much of a difference.

I disagree that upper level status is a must when it comes to upgrades.
Chain hotels are most definitely cognizant of the importance of customer loyalty....which is why they have membership programs in the first place.
Desk clerks are instructed to 'take care of their own' whenever possible. Sure, the bigger the fish, the more effort will be put forth.
But even as a low utilization member in hotel chains I don't frequent I often see the benefits of membership.
The biggest factor is occupancy rate. But hotel chains want to breed customer loyalty as effectively and broadly as possible.

I didn't mean for it to sound like upgrades never happen unless you have elite status and it certainly does depend on occupancy (and length of stay), but if a property expects a certain number of members arriving on a given day, the upgrade priority is going to be given to the higher tier members. As someone who has been a base and high tier member in several programs, I've noticed quite a difference in the area of upgrades (status vs. no status). As a base member, I received few, if any, 'discernable' upgrades. They may have very well upgraded me but if they did, it was a minimal upgrade and I didn't notice much difference. On the other hand, as a diamond member I've been upgraded to suites at the Hyatts in Kauai and Shanghai, just as an example. That's not to say there aren't benefits to membership at any level, just that as far as upgrades are concerned, in my experience status does make a difference.

I got an email blast from Wyndham By Request very recently for a $50 rate for a basic room at the Wyndham Miami Beach. Since it was so cheap, I booked it. Keeping in mind this was my first stay as a WBR member, they upgraded me to an oceanfront room on an upper floor.