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.

• Jon Seigel: Yep. Even if the e-mail address is unique, it's not possible to hack it using composable DML and a single assignment SELECT. I get the error "A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed in a SELECT statement that is not the immediate source of rows for an INSERT statement." This is kinda unfortunate because it's a really clean solution when you know you're only affecting a single row.
• Andrei Rînea: I understand. However I can say SELECT @JNK = SomeColumn FROM SomeTable WHERE SomeOtherColumn = MatchesMultipleRows... So why wasn't this constraint applied here? Anyway thanks and I'll just declare a local table variable and be done with it. Life goes on. :)

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