How to make a stored procedure return the last inserted identifier

Question

Here I have a stored procedure that inserts a string, but how to make it return the last inserted identifier without making another request

CREATE PROCEDURE [dbo].[spInsertCriteriaItem] @GroupID int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; insert into CriteriaItem (CriteriaGroupID) VALUES(@GroupID) --I don't want to make another query here END 

Is it possible to do this

Answer 1

Using Sql Server, you can use the OUTPUT clause.

Sort of

 DECLARE @CriteriaItem TABLE ( ID INT IDENTITY (1,1), CriteriaGroupID INT ) insert into @CriteriaItem (CriteriaGroupID) OUTPUT INSERTED.ID VALUES(1) 

Answer 2

dunno that your transaction is without an additional request, but if you need data from the database, you must request it.

 CREATE PROCEDURE [dbo].[spInsertCriteriaItem] @GroupID int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; insert into CriteriaItem (CriteriaGroupID) VALUES(@GroupID); SELECT SCOPE_IDENTITY(); END 

Answer 3

Why don't you want to make another request?

 CREATE PROCEDURE [dbo].[spInsertCriteriaItem] @GroupID int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; insert into CriteriaItem (CriteriaGroupID) VALUES(@GroupID); SELECT @@IDENTITY AS InsertedId; END