Query with variables

Question

Is it possible to set/read variables from within the query?

pseudo code:

SELECT animal_name,
    @tallest_animal = (select top 1 height from animal order by height desc) as tallest,
    @smallest_animal = (select top 1 height from  animal order by height asc) as smallest
FROM animals
WHERE height BETWEEN @smallest_animal AND @tallest_animal

I know the result can be achieved by making the query different, my question's real use is too difficult to explain.

It's Microsoft SQL Server in question. :)

Answer 1

It is not possible for a select statement to assign values to variables and return a resultset in the same SELECT statement - this is a restriction of SQL Server. Wouldn't it be great if it were possible!

Why do you wish to use variables here, if you're requiring a single statement? Would the following not work for you?

WITH cte (tallest, smallest) AS (
    SELECT MAX(height), MIN(height) FROM animals
)
SELECT animal_name FROM animals, cte WHERE height BETWEEN smallest AND tallest

If you wish to use the variables later on in a stored procedure, then your only option is to use two select statements: One for the assignment and one for the select:

DECLARE @tallest INT, @smallest INT
SELECT @tallest = MAX(height), @smallest = MIN(height) FROM animals
SELECT animal_name FROM animals WHERE height BETWEEN @smallest AND @tallest

Note that when using ADO, you can use compound queries in an ADO command. In other words, your command component can include more than one statement, so both of the above solutions would work.

Answer 2

Yes you can set variables within a query. Your syntax is actually quite close.

To do so you need:

SELECT @YourVariable = Column
FROM Animals

Note: You cannot use the AS when assigning a field to a variable.

You must ensure that all of the fields in the query are assigned to a variable, otherwise you will get the following error:

A SELECT statement that assigns a value to a variable must not be combined with data- retrieval operations.

To overcome this, simply assign AnimalName to an @AnimalName variable.

Edit:

DECLARE @AnimalName  VARCHAR(20)
DECLARE @TallestAnimal  INT
DECLARE @SmallestAnimal INT

SELECT @AnimalName = animal_name,
   @TallestAnimal  = (select top 1 height from animal order by height desc),
   @SmallestAnimal = (select top 1 height from  animal order by height asc) 
FROM animals
WHERE height BETWEEN @SmallestAnimal AND @TallestAnimal 

This code is assuming the height field is of type INT.

Answer 3

No, it is not possible, instead use like this:

DECLARE @tallest_animal int, @smallest_animal int
SET @tallest_animal=(SELECT max(height) from animals)
SET @smallest_animal=(SELECT min(height) from animals)
SELECT animal_name from animals where height between @tallest_animal AND @smallest_animal

Something like this will work but I am not sure about what you are looking for.

Answer 4

You can use derived tables instead of variables.

select A.animal_name, M.tallest, M.smallest
from animals A
  inner join 
      (
        select max(height) as tallest,
               min(height) as smallest
        from animal
      ) M
    on A.height between M.smallest and M.tallest