CODEWITHSUNDEEP
sqlsql-serverstored-procedures
loveprogramming
loveprogramming

Reputation: 578

Select @@identity while inserting possible?

I have this query which doesn't run correctly but it shows what I am trying to achieve:

INSERT INTO Table1
    (first_name, last_name, email, password_encrypt)

    VALUES
    (
    @first_name,@last_name,@email,
    EncryptByKey(Key_GUID('CertificateName'), @password_encrypt, 1, HashBytes('SHA1', CONVERT( varbinary, Output Inserted.UserID)))
    )

I would like to get the last inserted value produced by this insert statement and pass it where the Output Inserted.UserID is. How can that be done?

I can do this in two steps by selecting @@identity after the insert and then update the password column in the table. But I would love to do this in one go if it is possible at all.

My encryption is based on this article from Microsoft. http://technet.microsoft.com/en-us/library/ms179331.aspx Thanks.

Upvotes: 0

Views: 133

Answers (1)

Nicholai
Nicholai

Reputation: 818

The OUTPUT clause doesn't have any data for Inserted.* until after the insert has occurred. You will have to perform the insert first. If you try to work around it by nesting the INSERT into an UPDATE or MERGE query, SQL will give an error that it doesn't support that either.

One option is @@identity like you say. Another is to consider using the SEQUENCE object to define UserID. Its advantage in this case is that you can generate a UserID prior to the insert, and just like IDENTITY, there is no potential for conflict.

Upvotes: 1

Related Questions