return @@identity within stored procedure?

[k2]

is it possible to return an @@identity variable within a stored procedure (maybe with a trigger?) ? i'm trying to find a definitive answer but i'm not coming up with anything.

[k2]

//answer: child stored procedure looking for row_id value.

answer: SELECT SCOPE_IDENTITY()

[dang]

thank you

[znaps]

I'm confused who is asking and who is answering, but the answer at least in SQL Server is yes, it is possible. SELECT @@identity should work.

[k2]

i was asking the question; because i couldn't think of a method. i checked out SCOPE_IDENTITY() but the help shows it only in triggers so i figured a child sp executed within the sp that needs the identity would be the best method.

i wonder if you can use SCOPE_IDENTITY() inside an sp? i haven't bothered trying yet.

--

can you select @@identity from an sp? i didn't think that was possible since it's pulled from the inserted table.

[k2]

this is what i have in place now, and it works great. can i change it so it's not using a child sp?

CREATE PROCEDURE sp_Property_Insert_Agent
@AuthID nvarchar(40), @City int, @Username nvarchar(255), @Password nvarchar(255), @Email nvarchar(255), @FirstName nvarchar(255), @LastName nvarchar(255), @Telephone nvarchar(255), @Agency nvarchar(255), @License nvarchar(255), @IP nvarchar(255) AS

/* Inserting into the AXUsers table */
INSERT INTO AXUsers (Auth_ID, City, Nationality, DOB, Industry, Income, Male, Username, [Password], Email, Register_Email, Register_IP) VALUES(@AuthID, @City, 0, Year(GetDate()), 0, 0, 1, @Username, @Password, @Email, @Email, @IP)

/* Returning PK value of inserted account */
DECLARE @UserID int
EXEC sp_Users_ReturnID @Username, @AuthID, @UserID OUTPUT

/* Inserting into Property Agent table */
INSERT INTO Property_Agent ([User], FirstName, LastName, Telephone, Agency, License) VALUES(@UserID, @FirstName, @LastName, @Telephone, @Agency, @License)
GO

----

CREATE PROCEDURE sp_Users_ReturnID
@Username nvarchar(255), @AuthID nvarchar(40), @UserID int OUTPUT AS

SET @UserID = (SELECT TOP 1 User_ID FROM AXUsers WHERE (Active = 1) AND (Username = @Username) AND (Auth_ID = @AuthID) ORDER BY User_ID DESC)
GO

[znaps]

I've used select @@identity in SPs many times so I don't think you'll have any problems.

Also, I think you can return the @@identity for a particular table, which is what I think you might want, not the 'global' last inserted id.

I think you should make sp_Users_ReturnID into a function instead of an SP also, unless I'm missing something.

[k2]

would a function be faster? i've been curious about that myself. ill try the scope_identity() right now since it's scope is for the current execution.

[k2]

i changed the following and it worked; i should've tried it originally.

SET @UserID = (SELECT SCOPE_IDENTITY())

[openbsd-flipp]

Damn K2, I love how you answer your oen questions. about the time I get to the page where you posted one you have already answered it for me! Wish I could help a bit more often