SQL assign value to variable from multiple Select results-CodePudding

I have a question about how SQL assigns a value to a variable if the Select statement returns multiple results. For example if the following Select statement returns 5 rows, which one is assigned to the variable.

declare @i int
SELECT @i = age
  FROM student

CodePudding user response：

In your example you will end up with the last value returned when the statement is finished. Without a proper ORDER BY your results can be random because it depends on the last record returned.

You can also use = to get a cumulative value.

Below example works in SQL Server.

DROP TABLE IF EXISTS #Student
CREATE TABLE #Student
(
    Id INT IDENTITY(1,1)
    , Age INT
)

INSERT INTO #Student VALUES
(20), (21), (30), (26), (50)

DECLARE @i INT

/*  Update with each record returned  */
SELECT @i = Age
FROM #Student AS S
ORDER BY S.Age

SELECT @i AS SingleValue

/*  Cumulative value  */
SET @i = 0

SELECT @i  = Age
FROM #Student AS S
ORDER BY S.Age

SELECT @i AS CumulativeValue

CodePudding user response：

Above, youll get the last value as result since for many records that i will get updated each time

In short, Thats when you can make use of CURSOR when Sql query returns multiple results or you could also make use of ARRAY for storing multiple homogeneous objects or RECORD for storing multiple heterogeneous objects in Pl/sql