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

What are you trying to achieve through this query? Explain in words plz :) — Milee, Apr 18 '12 at 09:23
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. — freand, Apr 18 '12 at 10:14

score 9 · Answer 1 · edited Nov 25 '12 at 08:40

9

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.

edited Nov 25 '12 at 08:40

McGarnagle

101,349
31
229
260

answered Apr 18 '12 at 09:19

Darren

68,902
24
138
144

Thanks for all replies... give me a minute to test it :) – freand Apr 18 '12 at 10:04
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 – freand Apr 18 '12 at 10:09
@freand - 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". – Darren Apr 18 '12 at 10:11
Right! Thats one way to do it... but i have ~40 columns .... assigning all to variables will be a hassle :) but thanks anyhow – freand Apr 18 '12 at 10:18
@freand lol okay. I gave you a solution based on your Animal problem. Anyways hope it helped. – Darren Apr 18 '12 at 10:20

score 7 · Answer 2 · edited Aug 09 '13 at 11:34

7

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.

edited Aug 09 '13 at 11:34

MothFeatures

118
1
5

answered Apr 18 '12 at 09:22

Milee

1,191
1
11
29

Nah... i need to set and read a variable from within the same query. :( – freand Apr 18 '12 at 10:11

Mikael Eriksson · Answer 3 · 2012-04-18T10:01:04.417

5

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

edited Apr 18 '12 at 10:01

answered Apr 18 '12 at 09:55

Mikael Eriksson

136,425
22
210
281

score 1 · Answer 4 · answered Apr 18 '12 at 10:33

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.

Query with variables

4 Answers4

Linked