Returning id from SQL Stored Procedure

Im creating an app in VB.NET that calls a stored procedure which inserts data into one of my tables. I need it to return the id it creates (autonumber) to the VB.NET Program

I keep raising an exception in VB.NET...I want to make sure my stored procedure is correct first...sadly this is my first procedure ive created.

CREATE PROCEDURE db_addtocontractInsert
@contractid int,
@playerid int,
@numyears smallint,
@year1 float(8),
@year2 float(8),
@year3 float(8),
@year4 float(8),
@year5 float(8),
@yearsremain smallint
AS
INSERT INTO db_Contracts
VALUES (@playerid,@numyears,@year1,@year2,@year3,@year4,@year5,@yearsremain)

SELECT @contractid
Return @contractid
GO

-----------------------------------------------
Any tips or corrections that need to be made would be greatly appreciated.

Thanks

Tainter

Try:

CREATE PROCEDURE db_addtocontractInsert
@playerid int,
@numyears smallint,
@year1 float(8),
@year2 float(8),
@year3 float(8),
@year4 float(8),
@year5 float(8),
@yearsremain smallint
AS
INSERT INTO db_Contracts
VALUES (@playerid,@numyears,@year1,@year2,@year3,@year4,@year5,@yearsremain)

Return @@identity
GO

Alternatively, you might want contractid as an output parameter in which case you would only need to change your original code as follows:

@contractid int, => @contractid int OUTPUT,

SELECT @contractid => SELECT @contractid=@@identity