>So, if I needed to get more than one value from the table into more than one variable, how would you do it?

If I have two variables I want to populate, *and* if I know the query is optimized, I've done this.

SET @var1 = (select somecolumn ...)
SET @var2 = (select somecolumn ...)

Again, when the intention is to query 1 row. Call it strong preference, but I don't like the SELECT @var = column approach.

Now, if you start to ask, "what if you need to populate 5-6 variables, that means you'd be querying the same row again and again". Yes, that is true, though again, I would expect it to be optimized and you're likely pulling from the buffer pool.

Having said that, it I wanted to populate 5-6 (or more) variables, then it sounds more like I'm wanting to pull a row structure - in that case, I'd use some basic result set mapping approach (like a .NET list/collection, ADO.NET data table, etc.) and wouldn't use variables.

That's my way of saying, "If someone wants to pull all the columns into variables", I'd argue they need to be materializing those back in the application layer with some kind of basic list structure.

Now, if someone wants to say, "I prefer the SELECT @var1 = column1, @var2 = column approach.....I'll guarantee in my query that I'll only pull one row, with a TOP 1 or some other mechanism", I can't really make a compelling argument against it.