Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
SELECT does not find record
Message
De
22/11/2021 03:30:46
 
 
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Divers
Thread ID:
01682735
Message ID:
01682761
Vues:
31
>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.
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform