Direct the OUTPUT of an UPDATE statement to a local variable

Question

I would like to do this :

DECLARE @Id INT;

UPDATE Logins
SET    SomeField = 'some value'
OUTPUT @Id = Id
WHERE  EmailAddress = @EmailAddress -- this is a parameter of the sproc

Is this even possible? I know I can declare a local table variable and direct the output there but I would prefer to skip it if possible

Answer 1

No, because you are potentially OUTPUTting multiple rows, which wouldn't fit into a scalar variable.

You need to output into a @Table variable or declared table to handle multiple rows of output.

Answer 2

declare @status_atividade bit;

update t1 set         
    t1.idioma = t2.idioma, 
    t1.regiao = t2.regiao, 
    t1.fuso_horario = t2.fuso_horario,
    @status_atividade = t2.status_atividade

from 
    @usuario as t1  
join 
    dbo.locatario as  t2 
on 
    t1.id_locatario = t2.id_locatario

select @status_atividade