Query with variables

query-variables, sql, sql-server, subquery

Solution

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.

Problem

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. :)

Original source