0

I'm building a generic parameterized query (INSERT, or UPDATE) and I'm using parameters for the inserted columns (in the INSERT query) and parameters for the updated columns and the where clause columns (in the UPDATE query).

In either case, I also allow for a column name to be specified which is returned by my method, and to accomplish that I'm using OUTPUT INSERTED.ColumnName. This column could be the primary key of the table, but it could be also something else.

Now, after doing the INSERT or UPDATE, I want to obtain the key of the row that was just inserted or updated. How can I do this? Can I do something like OUTPUT INSERTED.ColumnName, SCOPE_IDENTITY()?

Note: This is NOT a stored procedure, in case that matters.

user2320724
  • 666
  • 3
  • 10
  • 18
  • 2
    can you show us some code?? aaaaaand btw, have you seen entity framework?? – Misters Nov 15 '13 at 01:20
  • `SCOPE_IDENTITY()` returns a single value representing the last identity value inserted in the current scope. I'm not convinced that it makes sense in an `OUTPUT` clause that can handle multiple rows and where the underlying table might not even have an IDENTITY column. – HABO Nov 15 '13 at 02:52
  • You cannot have multiple `OUTPUT` keywords, but you can output multiple **columns**, like `OUTPUT Inserted.ColumnName, Inserted.ID` etc. – marc_s Nov 15 '13 at 05:59

1 Answers1

1

Sure you can. It would look a bit like this:

DECLARE @result TABLE (
    ID INT,
    SomeColumn NVARCHAR(100)
)

INSERT SomeTable ( SomeColumn )
OUTPUT SCOPE_IDENTITY(), INSERTED.SomeColumn 
INTO   @result
VALUES ( 'Foo' ),
       ( 'Bar' )

SELECT * FROM @result

This would output:

ID | SomeColumn
---------------
 1 | 'Foo'
 2 | 'Bar'
p.s.w.g
  • 146,324
  • 30
  • 291
  • 331
  • Your syntax looks different, is that a stored procedure? I am using a parameterized query in C# and currently using `command.ExecuteScalar()`. Would that still work for outputting 2 values? Or will I have to use `ExecuteReader()`? – user2320724 Nov 15 '13 at 01:41
  • @user2320724 It's not a stored procedure, but you won't be able to use `ExecuteScalar`, because that will only ever return a single value. You'd have to use `ExecuteReader`. – p.s.w.g Nov 15 '13 at 01:46
  • If there are no triggers on the table you can skip the tablevariable, into and select clauses. just do `INSERT ... OUTPUT inserted.Id, inserted.SomeColumn VALUES ...` – adrianm Nov 15 '13 at 13:41