Retruning Clause ?

• Shivsam

  Old Hand

  Points: 330

  More actions

  October 20, 2004 at 12:16 am

  #60118

  Hi,

  In Oracle, the below query is valid :

  insert into test(sno,sno1) values(1,2) returning id into val_ret;

  // The above query in Oracle returns the affected row id's to the PL/SQL or host vriable(val_ret)

  Is there any equivalent for the above in SQL Server ?

  Please advice,

  Thanks,

  Shivsam

• Phil Parkin

  SSC Guru

  Points: 246964

  More actions

  October 20, 2004 at 12:57 am

  #527275

  Though 'retruning' is a great-sounding word, I think you mean returning?

  Great question - I'm assuming that you're performing an insert to a table with an auto-incrementing PK and want to know what the value of the PK will be, for this inserted record, after insert?

  I'd be interested to hear what other people here say - I have always ensured that I have inserted something unique (and known at time of insert) and then done a select query afterwards to determine the value of the new record's PK after insert.

  Regards

  Phil

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  October 20, 2004 at 2:10 am

  #527279

  If by 'row id' SQL Server's identity is implied, you can use either of SCOPE_IDENTITY, IDENT_CURRENT, or @@IDENTITY.

  They are slightly different in how they work, look up 'SCOPE_IDENTITY' in BOL and read there. There's a good explanation there about the differences.

  /Kenneth