@@identity not returning any value [resolved]

[thebloke]

Can any one see what's wrong with this? I'm calling it from an asp page. It's inserting the value but not returning anything in the recordset......

CREATE PROCEDURE spVan_Start AS

insert into tblVan_counter (q1) values (1)

select @@identity
GO

[brucevde]

Add a Set NoCount On statement before the Insert statement. Or better yet, return the new identity value in an Output parameter and eliminate the overhead of a recordset.

[Blade]

If you're using SQL Server 2000 try returning:

SELECT SCOPE_IDENTITY()

instead as @@IDENTITY is global and will not necessarily return you the ID of the table you just inserted into (especially if you have triggers on that table that insert into another table!)

[thebloke]

combined both and it works:

CREATE PROCEDURE spVan_Start AS

SET NoCount On

insert into tblVan_counter (q1) values (1)

select SCOPE_IDENTITY()
GO

Thanks guys