SQL select values into multiple variables from different rows in same table

Question

I have a table that is structured like this:

|ROWID |COUNTRY|PRICE|
|1     |US     |15|
|1     |UK     |10|
|1     |EU     |12|

I need to select the values from multiple rows into variables for use throughout a stored procedure, doing something like this:

DECLARE @USprice int,@UKprice int
SELECT @USprice = T.Price FROM Table T WHERE T.Country = 'US'
SELECT @UKprice = T.Price FROM Table T WHERE T.Country = 'UK'

The question is what's the fastest way to run this select? In reality I need to get about a dozen rows so am concerned about performance.

Would it be better to do this:

DECLARE @USprice int,@UKprice int
SELECT @USprice = CASE WHEN T.Country = 'US' THEN T.Price ELSE @USprice END
,@UKprice = CASE WHEN T.Country = 'UK' THEN T.Price ELSE @UKprice END
FROM Table T WHERE T.Country IN ('US','UK')

Which dbms are you using? (That code doesn't look like ANSI SQL.) — jarlh
– jarlh, Commented Oct 11, 2016 at 9:47
Both of the approaches will work,but you will be getting last values assigned to variables,if price returns more than one value.You can join this tables,instead of assigning variables — TheGameiswar
– TheGameiswar, Commented Oct 11, 2016 at 9:57
Also you can use Space and enter as well for formatting,no need of html ..stackoverflow.com/editing-help — TheGameiswar
– TheGameiswar, Commented Oct 11, 2016 at 10:05

Pரதீப் · Accepted Answer · 2016-10-11 10:00:44Z

2

I will not go with the first approach which require individual table hit for each variable which is not efficient

Here is the correct way to achieve the goal using your second approach. You need to add Min/Max aggregate on top of your case statement.

DECLARE @USprice INT,
        @UKprice INT
SELECT @USprice = Max(CASE WHEN T.Country = 'US' THEN T.Price ELSE NULL END),
       @UKprice = Max(CASE WHEN T.Country = 'UK' THEN T.Price ELSE NULL END)
FROM   Table T
WHERE  T.Country IN ( 'US', 'UK' )

Considering the T.Country column is not duplicated

edited Oct 11, 2016 at 10:00

answered Oct 11, 2016 at 9:50

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MatBailie Over a year ago

For the OP's benefit ; Not putting ELSE in there creates an implicit ELSE NULL. And then NULL is always treated "last" in MAX().

Pரதீப் Over a year ago

@MatBailie - added to the solution

Collectives™ on Stack Overflow

SQL select values into multiple variables from different rows in same table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related