In Stored Proc - How do i find the next key value (integer) and use it to populate a field

Currently I have the following stored procedure which simply adds a new row in my SQL Express 2005.

What I want is that -

1). before inserting the record find out the new ID (primary key) value. (ID is automatically a sequential integer generated by SQL Server)

2). and set COMPANY_ID = (new) ID

Any thoughts?

Thanks

ALTER PROCEDURE usp_tbl_Company_Insert
@.Company_ID int,
@.Name varchar(200),

AS

<FIND THE NEW ID of the new row in the database>

@.Company_ID = (new ID)

INSERT INTO tbl_Company (Company_ID, Name,)
VALUES (@.Company_ID, @.Name)

If its an IDENTITY column then you cannot insert any value into the field. SQL Server will insert that value for you. If you want to find out the value that was inserted use SCOPE_IDENTITY() function immediately after the insert. You can also get the value into a variable and use it accordingly.