stored procedure/Identity field

[vbmorton]

Was wonderin if anyone can help me, im trying to retrieve the identity value of a field x while doing an add to the table, in my vb6 code i call this stored procedure using a class module, how could i retrieve the value of my identity (field x) back, its not part of my add to the table its just in the table as counter.
any ideas?

CREATE PROCEDURE [sp_Projectnoteadd]
	(@projectno_1 	[int],
	 @notedate_2 	[datetime],
	 @note_3 	[text])

AS INSERT INTO [dbo].[ProjectNotes] 
	 ( [projectno],
	 [notedate],
	 [note]) 
 
VALUES 
	( @projectno_1,
	 @notedate_2,
	 @note_3)

 

[hmckillop]

change the stored proc to be

CREATE PROCEDURE [sp_Projectnoteadd]
    (@projectno_1     [int],
     @notedate_2     [datetime],
     @note_3     [text],
     @po_MyIdentity bigint OUTPUT)

AS INSERT INTO [dbo].[ProjectNotes] 
     ( [projectno],
     [notedate],
     [note]) 
 
VALUES 
    ( @projectno_1,
     @notedate_2,
     @note_3)
SELECT @po_MyIdentity= SCOPE_IDENTITY()

You will need to add a new parameter to hold the new return field, then the result will be in the additional parameter.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[vbmorton]

hmckillop,

Thanks for responding, I tried using that.. and it tells me that scope_identity() is an invalid function?

 

[bjd4jc]

I would use ident_current('tablename') instead of @@IDENTITY OR SCOPE_IDENTITY