Post reply

Retruning Clause ?

  • 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

  • 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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply