Search notes:

SQL Server: assign values to a variable with a select statement

The select statement can be used to assign values to a set of variables. Thus, a set statement to assign values to a variable is not necessary:

declare
   @val_1  varchar(20),
   @val_2  varchar(20),
   @val_3  integer;

select
   @val_1  = 'Hello World, ',
   @val_2  = 'the number is: ',
   @val_3  =  42;

print(@val_1 + @val_2 + trim(str(@val_3)));

Github repository about-MSSQL, path: /sql/select/assign-variables/without-from.sql

A select statement that uses a from clause can return its values to variables as well. Note: this even works if the select statement returns more than one record. The assigned values will then be the ones of the «last» record:

create table #tq84_values (
   val_1 varchar(10),
   val_2 float
);

insert into #tq84_values values
  ('one'  , 1),
  ('three', 3),
  ('seven', 7);

declare
   @max_val_1  varchar(10),
   @avg_val_2  float;

select
   @max_val_1 = max(val_1),
   @avg_val_2 = avg(val_2)
from
  #tq84_values;

print('max val_1 = ' + @max_val_1);
print('avg val 2 = ' + str(@avg_val_2, 5, 3));

Github repository about-MSSQL, path: /sql/select/assign-variables/from.sql

In order to assign a result set (with multiple records) to a variable, the table data type should be used.

Index