CODEWITHSUNDEEP
sqlsql-server
Fabrizio Manca
Fabrizio Manca

Reputation: 1

Use column value as variable name in where clause [SOLVED]

Do we have any option to use a variable that is dynamically generated on where clause depending on column value?

Example:

DECLARE @FR int(11) = '12'; 
DECLARE @GB int(11) = '13'; 
DECLARE @IT int(11) = '27'; 
DECLARE @ES int(11) = '20';
/****** countries variables are automatically generated with a php loop from an array so they could be even more than 200  ******/

select 
    country,
    price
from Statistics
where
    price <= @country /* so, this variable must have the country column value as example @FR if the country value is FR*/

Do we have any chance to do it?

More details:

 _________________
| country | price |
|    GB   |   25  | /* NOT SHOWN as > @GB*/
|    FR   |   10  | /* SHOWN as <= @FR */
|    IT   |   15  | /* SHOWN as <= @IT */
 _________________

[SOLUTION] At the end i created a #temp table with the countries and price data, joined to the main statement and compared. It works perfectly. Thanks a lot

Upvotes: 0

Views: 298

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

You can use boolean logic:

select country, price
from Statistics
where (price <= @fr and @country = 'FR') or
      (price <= @gb and @country = 'GB') or
      (price <= @it and @country = 'it') or
      (price <= @es and @country = 'es');

However, a more natural way to phrase this would use tables:

create table country_prices as (
    country varchar(2),
    price int
);

insert into country_prices (country, price)
    select 'FR', 12 union all
    select 'GB', 13 union all
    select 'IT', 27 union all
    select 'ES', 20 ;

Then the query would just be a join:

select country, price
from Statistics s join
     country_prices cp
     on cs.price <= cp.price and
        cp.country = @country;

Upvotes: 1

zip
zip

Reputation: 4061

You can do it usng case when

select 
    country,
    price
from Statistics
where
case when country = @FR then price <= country_fr
when country = @GB then price <= country_bg
...

when country = @IT then price <= country_it
end

Upvotes: 0

Related Questions