asked on

How to obtain Identity of second insert

This below returns the just inserted Identity of the first insert. I want the scope to return the Identy of the second insert

INSERT INTO Users (AspNetUserID, Active, CreateUserID) VALUES (@NewUserId, 1, 0)

            IF (NOT EXISTS (SELECT UserID from InstantForum414.dbo.InstantASP_Users
            WHERE InstantForum414.dbo.InstantASP_Users.EmailAddress = @Email
            OR InstantForum414.dbo.InstantASP_Users.Username = @UserName))


            BEGIN
            INSERT INTO InstantForum414.dbo.InstantASP_Users
                        (EmailAddress, Username, Password)
                        VALUES
                        (@Email, @UserName, @Password)

            SET @NewForumUserID = SCOPE_IDENTITY()

Guy Hengel [angelIII / a3]

try this:

INSERT INTO Users (AspNetUserID, Active, CreateUserID) VALUES (@NewUserId, 1, 0)
SET @NewUserID = SCOPE_IDENTITY()

IF (NOT EXISTS (SELECT UserID from InstantForum414.dbo.InstantASP_Users
WHERE InstantForum414.dbo.InstantASP_Users.EmailAddress = @Email
OR InstantForum414.dbo.InstantASP_Users.Username = @UserName))

BEGIN
INSERT INTO InstantForum414.dbo.InstantASP_Users
(EmailAddress, Username, Password)
VALUES
(@Email, @UserName, @Password)

SET @NewForumUserID = SCOPE_IDENTITY()
END

dba123

ASKER

We already have a way to retrieve the NewUserID so I don't want to code something that is just going to act as a decal.

dba123

ASKER

Isn't there some sort of SCOPE_IDENTITY().Next whereas it knows to start from the first insert and go to the next based on the entire script.

Mr_Peerapol

IDENT_CURRENT('your_table') will return the last generated identity ... so you could use the return value plus 1 for the next identity
However, in there might be other thread getting the same number

Mr_Peerapol

> However, in there might be other thread getting the same number
I mean in the case that you have concurrent connections executing your procedure at the same time ...

dba123

ASKER

that's not gonna work though. I am trying to get the Identity inserted into another database table. So there are possible conflicts using that as you suggested that I wan to stay away from.

ASKER CERTIFIED SOLUTION

Einstine98

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial