I wanna get @@Identity from a stored procedure.

[qwert231]

Here is what I have:

CREATE PROCEDURE dbo.MakeSession
@month int, @job int,
@MDate smalldatetime,
@SesID int OUTPUT
 AS 
INSERT INTO Sessions (EvntMonth, JobNumber, SessionDate)
VALUES (@month, @job, @MDate)
SELECT @SesID = @@IDENTITY

I want to get SesID from an ExecuteScalar call from ADO.Net. Basically, I want to get the new Primary key from this insert. I can do an EXEC and see the value, but I can't seem to get it out in ADO.NET. Any ideas?

 

[SQLSister]

WE usually return data with a select statement not an output variable.

put this at the bottom of your procedure:
Select @ses

BTW, @@identity is not the best choice if you want tobe sure you get the correct value back. If there iss a trigger on the table which als returns an identity value, then you will get that value instead of teh one you inserted into the orginal. Use scope_identity () instead.

 

[fixthebug2003]

or IDENT_CURRENT

 

[theoxyde]

Replace

SELECT @Ses = @@IDENTITY

and replace it with

SELECT SCOPE_IDENTITY()

You will probably need to cast the result explicitly as an integer in VB (ExecuteScalar only returns object).