Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2003
    Posts
    1

    Unanswered: can an INSERT statement RETURN a value?

    I only know a little bit of SQL for Access databases, so sorry if this is a silly question!

    Situation: I've to INSERT a new company in a SQL server table named 'companies'. This new company will automatically receive a unique ID (autonumber in Access terminology, don't know how to call it in SQL server)

    Question: Can this insert statement return the ID it gave to the company? Or how do I get this ID to use it in an other table?

    Thanks in advance!

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    create proc <blah-blah> (@blah1 varchar(blah), @blah2 varchar(blah-blah) )
    as
    declare @RetVal int, @Error int
    begin tran
    insert <blah> (blah1, blah2) values (@blah1, @blah2)
    select @Error = @@error, @RetVal = scope_identity()
    if @Error != 0 begin
    raiserror ('failed to insert into blah', 16, 1)
    rollback tran
    return 1
    end
    commit tran
    select NewIdentityValue = @RetVal
    return 0

    OR, you can define @RetVal as output parameter, this way you won't have to do a final SELECT. It's all up to your taste and preference.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...in other words, NO, it can't, but you can put your Insert statement in a procedure that will return a value or an Output parameter.

    blindman

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    it actually appears that the answer is YES. explanation blindman?

  5. #5
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    We're splitting hairs... yes you can get the ID... from the SELECT statement, no... from a stored procedure... yes...

    ClipChips asked if the statement could return the ID. The statement itself cannot. But if you use a SP, you can retrieve it either as a SELECT to a recordset, or an OUTPUT parameter.
    That which does not kill me postpones the inevitable.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It sounds like the answer is really our favorite "yes and no". Yes, @@error acts as a sort of return value, but in the strict definition of a return value, you can not have
    exec @retvalue = "insert into table values (...)" Does that explain it better?

  7. #7
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Yes, No?

    You can get Id back without stored procedure!

    create table test(id int identity primary key
    ,code varchar(10))
    go
    create trigger ins_test on test
    for insert
    as
    select id from inserted
    go
    insert test values('A')
    go
    id
    -----------
    1

    Just get recordset from command object....

  8. #8
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    True, but you need a trigger instead.. ... 6 and half a dozen.. take your pick
    That which does not kill me postpones the inevitable.

  9. #9
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Smile

    Originally posted by Seppuku
    True, but you need a trigger instead.. ... 6 and half a dozen.. take your pick
    But it is possible...

  10. #10
    Join Date
    Sep 2003
    Posts
    522
    you can do it even without a trigger, but from a command object.

    create table test1 (f1 int identity(1,1) not null, f2 char(1) not null)
    go

    Just assign the following command to it:
    insert test1 (f2) values ('A') select RetVal=@@identity -- or scope_identity() for sql2k

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •