how to get item data immediately after inserting

Hi

I have a form that allows users to insert items in the database. I call Scope_Identity() to get last identity value inserted. This works ok. When inserting the item a passowrd is randomly generated which is also saved in my database. I need to retrieve this password as well, immediately after the item is inserted. How do I do this?

From stackoverflow

• Execute a query to get the password based on the ID which you have just received by calling Scope_Identity().

  What technologies are you using?

  Anelim : Thanks for your help. I just thought there may be another way.

• pseudo code:

  insert into sometable
  declare @ID int  
  set @ID = Scope_Identity()
  select password from passwordtable where id = @ID
  

• Presumably you can get a reference to the new password before the insert takes place, which I think would be the best approach - this would prevent the need to do an INSERT followed by a SELECT to read out the password - removes the second step.

  e.g. Say you use NEWID() to generate a password, you'd do:

  DECLARE @Pwd VARCHAR(36)
  DECLARE @NewId INTEGER
  SELECT @Pwd = CAST(NEWID() AS VARCHAR(36))
  
  INSERT MyTable (SomeField, Pwd)
  VALUES (@SomeValue, @Pwd)
  
  SELECT @NewId = SCOPE_IDENTITY()
  

  @Pwd and @NewId would be OUTPUT parameters (I'd do this as a sproc). IMO this would be preferrable to doing an unnecessary read if at all possible.

  (NB. I'm ignoring the points about actually how to store passwords/not in plain text)

• IF you are using SQl Server 2008 you can use the output clause instead of scope identity. With the output clause you can output more than one field. an example of the code from Books online:

  DECLARE @MyTableVar table( ScrapReasonID smallint,
                             Name varchar(50),
                             ModifiedDate datetime);
  INSERT Production.ScrapReason
      OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
          INTO @MyTableVar
  VALUES (N'Operator error', GETDATE());