5

I am trying to get the Id of newly inserted record in Sqlserver. I have checked the Scope_Identity() but this is said to return the value of the autoincrement column. The column I am using for Id is Guid. I want this Guid column value after the insert. How should this be achieved? Or I must have to write a custom tedious function to get the Id of newly inserted record.

Pondlife
  • 15,992
  • 6
  • 37
  • 51
Spirals Whirls
  • 543
  • 1
  • 8
  • 27
  • 3
    I don't understand your issue. Where is the guid being created? Whether it is in your client code, or in a stored procedure in the database, simply use the value at the point where you create it. – Charles Bretana May 20 '13 at 17:02
  • have a look http://stackoverflow.com/questions/1509947/sql-server-scope-identity-for-guids – Sachin May 20 '13 at 17:03
  • 2
    OUTPUT http://msdn.microsoft.com/en-us/library/ms177564(v=sql.90).aspx – bummi May 20 '13 at 17:05
  • hhh.... This is called coding fever... Thanks buddy.. I am laughing at myself.. – Spirals Whirls May 20 '13 at 17:05

1 Answers1

13

You can try and use OUTPUT, it would be something like:

INSERT INTO ExampleTable (<Column1>, <Column2>, <Column3>) 
OUTPUT INSERTED.ID
VALUES (<Value1>, <Value2>, <Value3>)

Also, this question, has a lot more info on the different types of identity selection, you should check it out.

EDIT

You would use this as a regular scalar:

var command = new SqlCommand("...");
Guid id = (Guid) command.ExecuteScalar();
Dimitar Dimitrov
  • 14,868
  • 8
  • 51
  • 79
  • 2
    And how would you read this value returned in `OUTPUT INSERTED.ID` in C#? – Paul Feb 16 '18 at 20:43
  • 1
    @Paul You would use it in the same way as a scalar (it returns a single value). Check my Edit. – Dimitar Dimitrov Feb 21 '18 at 05:29
  • Ah, thank you. I didn't know `ExecuteScalar` could return anything other than an `int`. So you're saying you could do something like `Guid id = (Guid) command.ExecuteScalar();`, since the question is specifically asking how to return a `guid`. – Paul Feb 22 '18 at 18:34
  • 1
    @Paul Yeah absolutely, `ExecuteScalar` simply returns the first column from the first row from the result set, i.e. - a single value, it could be anything. Also thanks for the catch, I've updated the answer. – Dimitar Dimitrov Feb 22 '18 at 19:09
  • Could we use `command.ExecuteReader()` if we wanted to get back a list of GUIDs for *all the inserted rows* (i.e. if we insert multiple rows?) ([`ExecuteScalar` vs `ExecuteReader`](https://stackoverflow.com/a/2974203/1175496)) – Nate Anderson Apr 08 '21 at 16:54