Geek asks and answers

Query with variables

Is it possible to set / read variables from a 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 that the result can be achieved if you make the request different, my question is the actual use is too difficult to explain.

This is a Microsoft SQL Server question. :)

+3
source share
4 answers

Yes, you can set the variables in the query. Your syntax is actually pretty close.

For this you need:

SELECT @YourVariable = Column
FROM Animals

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

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

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

, AnimalName @AnimalName.

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

, INT.

+7

.

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
+4

, , :

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

- , , .

+3

select SELECT - SQL Server. , !

, ? :

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

, - 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 complex queries in the ADO command. In other words, your command component may include more than one statement, so both of the above solutions will work.

+1
source

More articles:


All Articles