getting the unique auto-incrementing id from a new row/mssql

[sde]

i have a table which increments the id field by 1.

 

what is the logic i would use to insert a new row and retrieve the new id?

 

do i have to use 2 queries?

[Robby]

Are you using SQL Server? if so, use @@Identity if your inserting in a stored proc.

[sde]

i am using sql server. i think i need to brush up on my db admin knowledge, cause i don't know what you really mean.

 

gives me a place to start my google search though. thanks :)

[Robby]

Create a Stored Proc to do your inserts. Here's a sample you can run in Query Analyzer....

 

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO
CREATE		PROCEDURE dbo.Some_Stored_Proc 
	@Fname varchar(30)= null,-- not required
	@Lname varchar(30)--required
AS
declare @id int
SET @id = 0

if (@Lname != '')--ensures a valid input
BEGIN
Begin transaction
INSERT INTO	Table1 
		([Fname],[Lname],[date_created])
Values		(@Fname, @Lname,getdate())

SET @id =  @@IDENTITY
commit transaction

return @id --return the new ID number to the caller
END

SET QUOTED_IDENTIFIER OFF

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO