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. :)
The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.
When a variable is first declared, its value is set to NULL. To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters.
You can't use a variable in an IN clause - you need to use dynamic SQL, or use a function (TSQL or CLR) to convert the list of values into a table.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With