Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Could someone with a reputation with 300 or more please add a tag called "OUTPUT" to this (if you agree, of course). Thanks! –  Andrei Rinea Aug 17 '12 at 19:34

1 Answer 1

up vote 11 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.

share|improve this answer
    
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 posting your answer, you agree to the privacy policy and terms of service.

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