Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Query with variables

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

like image 492
freand Avatar asked Apr 18 '12 09:04

freand


People also ask

How do you query a variable?

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.

How do you assign a SELECT query to a variable in SQL?

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.

What is %% in SQL query?

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.

Can you use a variable in in statement SQL?

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.


1 Answers

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.

like image 91
Darren Avatar answered Nov 13 '22 04:11

Darren