Getting rowId of recently inserted row

[silentthread]

Hi,

I have 2 SQL tables, and a user web form (aspx.net page) that when it gets filled out, it makes inserts into those 2 tables.

Example of table design...

Table 1
RowId, Name, LastName

Table 2
RowId, Comments


The problem is that after I make the first insert into Table1, I need to somehow get the row id of that row and use it in my second insert so I can link them.

Is there a way to do this in SQL?

[Tom Sawyer]

Create a stored procedure and pass in the parameters you need for both tables. Then insert into the first table, grab the identity value and use it to insert into the second table.

For instance:

CREATE PROCEDURE dbo.spUpdateStuff
(
@FirstName varchar(50),
@LastName varchar(50),
@Comments varchar(100)
)
AS

DECLARE @rowID int

INSERT Table1 (FirstName, LastName)
	VALUES (@FirstName, @LastName)

SELECT @rowID = @@IDENTITY

INSERT Table2 (RowID, Comments)
	VALUES (@rowID, @Comments)

GO