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

up vote 15 down vote accepted

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 Rinea Aug 17 '12 at 19:23

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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