11

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

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.

  • 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
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.