how to get item data immediately after inserting

Question

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?

Answer 1

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?

Answer 2

pseudo code:

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

Answer 3

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)

Answer 4

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());