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

Boomer

What are you trying to achieve through this query? Explain in words plz :) – Cymbiform

well its a complex query that gathers a lot of information based on different timestamps etc so i need to calulate prices and consumption rates all from witin a sql query soo i ended up makeing the same "subquerys" multiple times an thought it would be effective to set a variable and re-use it. – Boomer

Answer 1

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.

Single

Thanks for all replies... give me a minute to test it :) – Boomer

Tested.... "declare @test varchar(10) select *,@test = (select top 1 userid from users order by userid desc) from Transmission.dbo.users" Result....A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations. soo it doesnt work it seames – Boomer

@Boomer - read my comment about that in my post. It's because you have SELECT *. You need to assign all fields to a variable, like you have done with "@test". – Single

Right! Thats one way to do it... but i have ~40 columns .... assigning all to variables will be a hassle :) but thanks anyhow – Boomer

@Boomer lol okay. I gave you a solution based on your Animal problem. Anyways hope it helped. – Single

Answer 2

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.

Cymbiform

Nah... i need to set and read a variable from within the same query. :( – Boomer

Answer 3

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

Pede

Answer 4

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.

Rosenkranz