Moons Moons - 1 year ago 46
SQL Question

Store a single sql server row in variable and then use the column values to construct a query

I have a problem where the user has set his preferences in a table.

The table has more than 5 columns. Now I want to search the other view with condition values that are in the



table has
price range

So I need to search the products with price and colours.

Now I want to do it in SQL Server itself i.e. passing the preference id, it will always return a single row and then from the columns get the values like
min price
max price
and then create a search query.

The Issue is I dont know that how I can store the preference row column values in variables so that I can use it.

I am using Entity Framework so that I cannot using Dynamic SQL too.

I only want to know the way by which I can store the column values of preference table.

  • I only know that i can do it something like:

    @colour = Select Top 1 Colour from preferences;

But like this I need to write this query for every variable. Is there is some better way with something called as CTE etc.

Answer Source

You have to declare the variables, then you can use them to assign the values in the SELECT and use them later:

-- declare the variables
DECLARE @min_price decimal(8,2)
DECLARE @max_price decimal(8,2)
DECLARE @color varchar(20)

-- assign the values into the variables
SELECT @color = Colour, @min_price = MIN_PRICE, @max_price = MAX_PRICE
FROM Preference
WHERE preference_id = (parameter of prefrence id)

-- Now you get all 3 values of Colour, Min Price, Max Price,
-- then these 3 values can be used for your query

FROM products 
WHERE colour = @color 
    AND price BETWEEN min_price AND @max_price

Or you can make a single query if these 3 variables are not used any other place:

FROM products p
  FROM Preference
  WHERE preference_id = (parameter of prefrence id)
) pre ON (p.colour = pre.Colour AND p.price BETWEEN pre.MIN_PRICE AND pre.MAX_PRICE)