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

score 15 · Accepted Answer · answered Aug 17 '12 at 15:36

15

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.

answered Aug 17 '12 at 15:36

JNK

17,718
5
56
97

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. – Jon Seigel Aug 17 '12 at 16:36
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. :) – Andrei Rînea Aug 17 '12 at 19:23

score 0 · Answer 2 · edited May 16 '19 at 06:16

0

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

edited May 16 '19 at 06:16

tinlyx

2,735
4
29
53

answered May 16 '19 at 02:37

Rangel Borene

1

Direct the OUTPUT of an UPDATE statement to a local variable

2 Answers2