Re: How can I get the autonum in sproc?

• AJ-92543

  SSC Eights!

  Points: 852

  More actions

  April 15, 2005 at 10:56 am

  #164042

  Hi all,

  I like to find out how I can return a value from a record just inserted in sproc. I would like the new lotnum but instead I'm getting the old lotnum I think cuz the new record has not inserted yet. The lotnum is the PK and identity field. How can I get new lotnum then?  THanks much!

  CREATE PROCEDURE dbo.spFI_Add_data

   @sampler_id int,

   @sampling_id int OUT,

   @new_lotnum int OUT

   

   AS

  if @@rowcount = 1

  Begin

  INSERT INTO tblFinal_inspection

          (final_inspection_id, apple_po, invoice_num, .....)

  SELECT    dbo.tblFinal_inspection.final_inspection_id, dbo.tblFinal_inspection.apple_po, ..

  FROM         dbo.tblFinal_inspection INNER JOIN

                        dbo.tblSampling_data ON

  WHERE ...

  SELECT @new_lotnum = lotnum FROM dbo.tblFinal_inspection ORDER BY lotnum desc;

  End

• Chrissthom

  SSChasing Mays

  Points: 656

  More actions

  April 15, 2005 at 12:07 pm

  #552978

  Not sure but I am thinking it is because the transaction has not committed yet and won't see the value until it is committed ( the I in ACID ).

  I would think if you put an end in before doing the select, it would return the new value.

• PW-201837

  SSC-Insane

  Points: 20805

  More actions

  April 15, 2005 at 1:34 pm

  #553006

  Read BOL on topics SCOPE_IDENTITY and @@IDENTITY